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

Thanks! How would I find the highest score in the union of the two tables?

I tried this but it can't find unionTable:

SELECT * FROM
(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) as unionTable
WHERE unionTable.score= (SELECT max(unionTable.score) FROM unionTable);

Shane Ambler wrote:
> 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
> 
> 
> 
> 
>