Stuck in "group by" aggregate hell

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

Responses

Browse pgsql-novice by date

  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