Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: David OrmeDate: 2005-02-07 11:57:26
Subject: multiple sampling from tables and saving output
Previous:From: John K. HerreshoffDate: 2005-02-07 11:17:47
Subject: Re: simple query question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group