Re: SQL question: Highest column value of unique column pairs - Mailing list pgsql-sql

From Marc Mamin
Subject Re: SQL question: Highest column value of unique column pairs
Date
Msg-id CA896D7906BF224F8A6D74A1B7E54AB301750D2C@JENMAIL01.ad.intershop.net
Whole thread Raw
In response to SQL question: Highest column value of unique column pairs  (Kevin Jenkins <gameprogrammer@rakkar.org>)
List pgsql-sql
Hello Kevin,

I would use "select distinct on" to first isolate the candidates in (1)
and (2) and then reitere the query on this sub result:
(the query below will retrieve the last score, not the best one...)


something like (not tested):

select distinct on (date,name)
date,name,score
from    (select distinct (on date, LName1)          date,LName1 as name ,score1 as score  from table  order by date
desc,LName1   union all    select distinct on (date, LName2)          date,LName2 as name,score2 as score  from table
orderby date desc, LName2  )foo   
order by date desc,name


regards,

Marc Mamin




-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Kevin Jenkins
Sent: Saturday, January 12, 2008 1:10 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] SQL question: Highest column value of unique column pairs

Hi,

I have the following table which holds the result of 1 on 1 matches:

FName1, LName1, Score1, FName2, LName2, Score2, Date
John,   Doe,    85      Bill,   Gates,  20      Jan 1.
John,   Archer, 90      John,   Doe,    120     Jan 5
Bob,    Barker, 70      Calvin, Klien   8       Jan 8
John,   Doe,    60      Bill,   Gates,  25      Jan 3.

So columns 1 and 2 hold the first person. Column 3 holds his score.
Columns 4 and 5 hold the second person. Column 6 holds his score.

I want to return the most recent score for each person (be they an
opponent or myself).  And the resultant table shouldn't care if they are
person 1 or 2.

So the end result would be

FName,   LName, Score, Date
John,    Doe,   120    Jan 5.
John,    Archer 90     Jan 5.
Bob,     Barker 70     Jan 8
Bill,    Gates  25     Jan 3
Calvin   Klien  8      Jan 8

Thanks for any help!

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
               http://www.postgresql.org/about/donate


pgsql-sql by date:

Previous
From: Steve Midgley
Date:
Subject: Re: UTF8 encoding and non-text data types
Next
From: "Marc Mamin"
Date:
Subject: Re: SQL stored function inserting and returning data in a row.