From: Shane Ambler <pgsql@Sheeky.Biz>
To: Kevin Jenkins <gameprogrammer@rakkar.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL question: Highest column value of unique column pairs
Date: Sat, 12 Jan 2008 21:00:44 +1030
Message-ID: <478896D4.8090405@Sheeky.Biz> (raw)
In-Reply-To: <47882EAC.4030203@rakkar.org>
References: <4788056B.2020809@rakkar.org>
<47881810.7030303@Sheeky.Biz>
<47882EAC.4030203@rakkar.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql@Sheeky.Biz, gameprogrammer@rakkar.org
Subject: Re: SQL question: Highest column value of unique column pairs
In-Reply-To: <478896D4.8090405@Sheeky.Biz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox