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

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

the (select max(score)...) doesn't see the unionTable

change the last line to order by score desc limit 1

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

order by score desc limit 1

> 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
>>
>>
>>
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz