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