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