Re: Stuck in "group by" aggregate hell
От | Schuhmacher, Bret |
---|---|
Тема | Re: Stuck in "group by" aggregate hell |
Дата | |
Msg-id | 98E4F4D46DACD0479C96D7356D5C37356B045C@sac1exch3.aspect.com обсуждение исходный текст |
Ответ на | Stuck in "group by" aggregate hell ("Schuhmacher, Bret" <Bret.Schuhmacher@Aspect.com>) |
Список | pgsql-novice |
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. >
В списке pgsql-novice по дате отправления: