Skip site navigation (1) Skip section navigation (2)

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

From: Shane Ambler <pgsql(at)Sheeky(dot)Biz>
To: Kevin Jenkins <gameprogrammer(at)rakkar(dot)org>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: SQL question: Highest column value of unique column pairs
Date: 2008-01-12 10:30:44
Message-ID: 478896D4.8090405@Sheeky.Biz (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-sql
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

In response to

Responses

pgsql-admin by date

Next:From: Andreas HaumerDate: 2008-01-12 12:44:55
Subject: Restore dump into different databases/owners
Previous:From: Tino SchwarzeDate: 2008-01-12 09:40:42
Subject: Re: Fwd: physical memory

pgsql-sql by date

Next:From: Medi MontaseriDate: 2008-01-12 22:21:00
Subject: UTF8 encoding and non-text data types
Previous:From: Rajesh Kumar MallahDate: 2008-01-12 09:02:34
Subject: Re: (possible) bug with constraint exclusion

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group