From: | Kevin Jenkins <gameprogrammer(at)rakkar(dot)org> |
---|---|
To: | |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: SQL question: Highest column value of unique column pairs |
Date: | 2008-01-12 03:06:20 |
Message-ID: | 47882EAC.4030203@rakkar.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-sql |
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
>
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Antonio5556 | 2008-01-12 08:39:52 | Re: Fwd: physical memory |
Previous Message | Shane Ambler | 2008-01-12 01:29:52 | Re: SQL question: Highest column value of unique column pairs |
From | Date | Subject | |
---|---|---|---|
Next Message | Rajesh Kumar Mallah | 2008-01-12 05:00:43 | Re: (possible) bug with constraint exclusion |
Previous Message | Shane Ambler | 2008-01-12 01:29:52 | Re: SQL question: Highest column value of unique column pairs |