From: | "Schuhmacher, Bret" <Bret(dot)Schuhmacher(at)Aspect(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Stuck in "group by" aggregate hell |
Date: | 2005-02-07 11:34:03 |
Message-ID: | 98E4F4D46DACD0479C96D7356D5C37356B0457@sac1exch3.aspect.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | David Orme | 2005-02-07 11:57:26 | multiple sampling from tables and saving output |
Previous Message | John K. Herreshoff | 2005-02-07 11:17:47 | Re: simple query question |