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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq