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
>
>
>
>
>