Message-ID: <47881810.7030303@Sheeky.Biz>
Date: Sat, 12 Jan 2008 11:59:52 +1030
From: Shane Ambler <pgsql@Sheeky.Biz>
User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728)
MIME-Version: 1.0
To: Kevin Jenkins <gameprogrammer@rakkar.org>
CC: pgsql-sql@postgresql.org
Subject: Re: SQL question: Highest column value of unique column pairs
References: <4788056B.2020809@rakkar.org>
In-Reply-To: <4788056B.2020809@rakkar.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Kevin Jenkins wrote:
> 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!
> 

First I would say you should have one person in a row and have another 
table to join them like you want.


Try (untested just guessing) -

select fnam1 as fname,lname1 as lname, score1 as score
from myscorestable

union

select fnam2 as fname,lname2 as lname, score2 as score
from myscorestable

order by 3


-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz