SQL question: Highest column value of unique column pairs

From: Kevin Jenkins <gameprogrammer(at)rakkar(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: SQL question: Highest column value of unique column pairs
Date: 2008-01-12 00:10:19
Message-ID: 4788056B.2020809@rakkar.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-sql

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!

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Shane Ambler 2008-01-12 01:29:52 Re: SQL question: Highest column value of unique column pairs
Previous Message David Wall 2008-01-11 21:08:04 Re: WAL recovery, stop and resume recovery?

Browse pgsql-sql by date

  From Date Subject
Next Message Shane Ambler 2008-01-12 01:29:52 Re: SQL question: Highest column value of unique column pairs
Previous Message Tom Lane 2008-01-11 19:56:08 Re: [SQL] (possible) bug with constraint exclusion