From: Michael Glaesemann <grzm@seespotcode.net>
To: Mike Diehl <jdiehl@sandia.gov>
Cc: SQL Postgresql List <pgsql-sql@postgresql.org>
Subject: Re: Problem with phone list.
Date: Wed, 15 Aug 2007 16:04:37 -0500
Message-ID: <904C5251-F9A7-4579-B083-F77DDB4D6EDC@seespotcode.net> (raw)
In-Reply-To: <200708151428.17740.jdiehl@sandia.gov>
References: <200708151428.17740.jdiehl@sandia.gov>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@seespotcode.net, jdiehl@sandia.gov
Subject: Re: Problem with phone list.
In-Reply-To: <904C5251-F9A7-4579-B083-F77DDB4D6EDC@seespotcode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox