Re: SQL question: Highest column value of unique column pairs

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