Re: Problem with phone list.

On Aug 15, 2007, at 15:28 , Mike Diehl wrote:

> I've got a table, actually a view that joins 3 tables, that
> contains a phone
> number, a unique id, and a call duration.
>
> The phone number has duplicates in it but the unique id is unique.
>
> I need to get a list of distinct phone numbers and the
> coorisponding largest
> call duration.

If you don't need the id, the simplest thing to do is just

SELECT phone_number, max(call_duration)
FROM calls
GROUP BY phone_number;

However, I assume you want the id as well. My first thought is to use
PostgreSQL's DISTINCT ON (if you don't mind using non-SQL-standard
syntax):

SELECT DISTINCT ON (phone_number)
phone_number, call_duration, id
FROM calls
ORDER BY phone_number
, call_duration DESC;

Another way is to figure out the maximum duration for each phone
number and join this back to the full list.

SELECT id, phone_number, call_duration
FROM calls
NATURAL JOIN (
SELECT phone_number, max(call_duration) as call_duration
FROM calls
GROUP BY phone_number
) max_call_durations_per_number;

Two caveats: this either potentially returns more than one id per
phone number (if more than one call with the same phone number has
the same duration, which is also the max). If you add a DISTINCT (and
ORDER BY) to the subquery, you could get distinct numbers, but
potentially miss information.

Michael Glaesemann
grzm seespotcode net