Thread: Problem with phone list.
Hi all. I've qot a problem I need to solve. I'm sure it's pretty simple; I just can't seem to get it, so here goes... 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. I've got the idea that this should be a self-join on phone number where a.id<>b.id, but I just can't seem to get the max duration. Any hints would be much appreciated. Mike.
On 8/15/07, Mike Diehl <jdiehl@sandia.gov> wrote: > Any hints would be much appreciated. DDL + sample data, please...
Try this:
Select *
from view v1
where duration = (select max(duration) from view v2 where v2.phone_number =
v1.phone_number)
You could get more than one call listed for the same number if many calls
match max(duration) for that number.
-----Mensaje original-----
De: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
En nombre de Mike Diehl
Enviado el: Miércoles, 15 de Agosto de 2007 17:28
Para: SQL Postgresql List
Asunto: [SQL] Problem with phone list.
Hi all.
I've qot a problem I need to solve. I'm sure it's pretty simple; I just
can't
seem to get it, so here goes...
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.
I've got the idea that this should be a self-join on phone number where
a.id<>b.id, but I just can't seem to get the max duration.
Any hints would be much appreciated.
Mike.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
--- Mike Diehl <jdiehl@sandia.gov> wrote: > I've qot a problem I need to solve. I'm sure it's pretty simple; I just can't > seem to get it, so here goes... > > 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. > > I've got the idea that this should be a self-join on phone number where > a.id<>b.id, but I just can't seem to get the max duration. SELECT phone_number, max( duration ) as max_duration FROM your_view GROUP BY phone_number; if you need the unique Id also, SELECT DISTINCT ON ( phone_number ) id, phone_number, duration FROM your_view ORDER BY duration desc; or SELECT V1.id, V1.phone_number, V1.duration FROM your_view AS V1 INNER JOIN ( SELECT phone_number, max( duration ) FROM your_view GROUP BY phone_number ) AS V2( phone_number,duration ) ON (V1.phone_number, V1.duration) = (V2.phone_number, V2.duration); Regards, Richard Broersma Jr.
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
Yup, that did it. I don't know why I made it harder than it had to be. Thank you. Mike. On Wednesday 15 August 2007 02:58:22 pm Fernando Hevia wrote: > Try this: > > Select * > from view v1 > where duration = (select max(duration) from view v2 where v2.phone_number = > v1.phone_number) > > You could get more than one call listed for the same number if many calls > match max(duration) for that number. > > > -----Mensaje original----- > De: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] > En nombre de Mike Diehl > Enviado el: Miércoles, 15 de Agosto de 2007 17:28 > Para: SQL Postgresql List > Asunto: [SQL] Problem with phone list. > > Hi all. > > I've qot a problem I need to solve. I'm sure it's pretty simple; I just > can't > seem to get it, so here goes... > > 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. > > I've got the idea that this should be a self-join on phone number where > a.id<>b.id, but I just can't seem to get the max duration. > > Any hints would be much appreciated. > > Mike. > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org -- Mike Diehl
--- Michael Glaesemann wrote: > SELECT DISTINCT ON (phone_number) > phone_number, call_duration, id > FROM calls > ORDER BY phone_number > , call_duration DESC; Wasn't acquainted with "DISTINCT ON (column)". I found it to be many times faster than other suggestions using JOIN. Cheers, Fernando.