Re: Stuck in "group by" aggregate hell

Thank you, Stephan!  Both work great! :-)  I was not familiar with the
Postgres extension method you showed me.  I was also not aware you could
use a "where...in" clause with multiple data elements!  Thanks - I'll go
(re) read up on those things...

Thanks again!

Bret

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
> Sent: Monday, February 07, 2005 9:15 AM
> To: Schuhmacher, Bret
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Stuck in "group by" aggregate hell
>
>
> On Mon, 7 Feb 2005, Schuhmacher, Bret wrote:
>
> > I've got a table with the following schema:
> > Phone_num        latlon        location_when
> >
> > Each row holds a user's phone number, their location, and the time
> > they were at that location.  There can be up to 120 rows per
> > phone_num, each with a  (potentially) different latlon, and
> each with
> > a different location_when (the primary key).
> >
> > My problem - how do you get a list of each phone_num's most recent
> > position and time?  I want to weed out everything but a user's most
> > recent location, returning only one line per user.
> >
> > Here's an example:
> >
> > Phone_num            latlon            location_when
> > 1111111111            22.12345,-90.12345    0901
> > 1111111111            22.11111,-89.45678    0911
> > 1111111111            21.99999,-89.55555    0921
> > 2222222222            18.12334,-120.12345    1156
> > 2222222222            18.10101,-120.11111    1206
> > 2222222222            18.00001,-120.34889    1216
> >
> >
> > Given this, I want a list like this:
> > 1111111111            21.99999,-89.55555    0921
> > 2222222222            18.00001,-120.34889    1216
> >
> >
> >
> > Obviously, it's something along these lines:
> > Select *,min(age(now(),location_when)) From table Group by
> phone_num;
> >
> > Unfortunately, Postgres wants me to group by latlon and
> location_when,
> > either of which makes each row a unique entity and causes
> me problems.
> >
> > I'd prefer to not use temp tables, but at this point I'll take any
> > pointers I can get.  Intersect?  Some form of outer join
> with the same
> > table?
>
> I believe the SQL way is to correlate the outside with a
> subquery so if just using the maximum location_when were
> sufficient (and there aren't
> nulls) I think you could do something like:
>
> select * from table where (phone_num, location_when) in
> (select phone_num, max(location_when) from table group by phone_num);
>
> In PostgreSQL, there's an extension which lets you do this
> slightly better in which case maybe something like this:
>
> select distinct on (phone_num) * from table order by
> phone_num, location_when desc.
>