pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with phone list.
7+ messages / 5 participants
[nested] [flat]
* Problem with phone list.
@ 2007-08-15 20:28 Mike Diehl <jdiehl@sandia.gov>
0 siblings, 4 replies; 7+ messages in thread
From: Mike Diehl @ 2007-08-15 20:28 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problem with phone list.
@ 2007-08-15 20:49 Rodrigo De León <rdeleonp@gmail.com>
parent: Mike Diehl <jdiehl@sandia.gov>
3 siblings, 0 replies; 7+ messages in thread
From: Rodrigo De León @ 2007-08-15 20:49 UTC (permalink / raw)
To: Mike Diehl <jdiehl@sandia.gov>; +Cc: pgsql-sql
On 8/15/07, Mike Diehl <jdiehl@sandia.gov> wrote:
> Any hints would be much appreciated.
DDL + sample data, please...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problem with phone list.
@ 2007-08-15 20:58 Fernando Hevia <fhevia@ip-tel.com.ar>
parent: Mike Diehl <jdiehl@sandia.gov>
3 siblings, 1 reply; 7+ messages in thread
From: Fernando Hevia @ 2007-08-15 20:58 UTC (permalink / raw)
To: 'Mike Diehl' <jdiehl@sandia.gov>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problem with phone list.
@ 2007-08-15 21:00 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Mike Diehl <jdiehl@sandia.gov>
3 siblings, 0 replies; 7+ messages in thread
From: Richard Broersma Jr @ 2007-08-15 21:00 UTC (permalink / raw)
To: Mike Diehl <jdiehl@sandia.gov>; pgsql-sql
--- 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problem with phone list.
@ 2007-08-15 21:04 Michael Glaesemann <grzm@seespotcode.net>
parent: Mike Diehl <jdiehl@sandia.gov>
3 siblings, 1 reply; 7+ messages in thread
From: Michael Glaesemann @ 2007-08-15 21:04 UTC (permalink / raw)
To: Mike Diehl <jdiehl@sandia.gov>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problem with phone list.
@ 2007-08-16 06:11 Mike Diehl <jdiehl@sandia.gov>
parent: Fernando Hevia <fhevia@ip-tel.com.ar>
0 siblings, 0 replies; 7+ messages in thread
From: Mike Diehl @ 2007-08-16 06:11 UTC (permalink / raw)
To: Fernando Hevia <fhevia@ip-tel.com.ar>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Problem with phone list.
@ 2007-08-16 13:56 Fernando Hevia <fhevia@ip-tel.com.ar>
parent: Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 0 replies; 7+ messages in thread
From: Fernando Hevia @ 2007-08-16 13:56 UTC (permalink / raw)
To: 'Michael Glaesemann' <grzm@seespotcode.net>; 'Mike Diehl' <jdiehl@sandia.gov>; +Cc: pgsql-sql
--- 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-08-16 13:56 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-15 20:28 Problem with phone list. Mike Diehl <jdiehl@sandia.gov>
2007-08-15 20:49 ` Rodrigo De León <rdeleonp@gmail.com>
2007-08-15 20:58 ` Fernando Hevia <fhevia@ip-tel.com.ar>
2007-08-16 06:11 ` Mike Diehl <jdiehl@sandia.gov>
2007-08-15 21:00 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-15 21:04 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-16 13:56 ` Fernando Hevia <fhevia@ip-tel.com.ar>
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