DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=tzLMI74MaJcRj/sjHRLbeW+kAdTEHwQfSZgB2g/EUpxFd2x8lUI38xz+HpP1mdQa3Q4Xi2slR5pk6F0y1FO6KxLThAd6YkJSdLxbEB4MnFvRsy7G8TBbqsYdZw9eBqHv+kgmISkTY9wMBJGpZPy9pT0Kx18WRC0E/5QB1OVHYII=;
Date: Wed, 15 Aug 2007 14:00:05 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Problem with phone list.
To: Mike Diehl <jdiehl@sandia.gov>,
	SQL Postgresql List <pgsql-sql@postgresql.org>
In-Reply-To: <200708151428.17740.jdiehl@sandia.gov>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <846214.97432.qm@web31807.mail.mud.yahoo.com>


--- 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.