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 01:29:52 |
Message-ID: | 47881810.7030303@Sheeky.Biz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-sql |
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
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Jenkins | 2008-01-12 03:06:20 | Re: SQL question: Highest column value of unique column pairs |
Previous Message | Kevin Jenkins | 2008-01-12 00:10:19 | SQL question: Highest column value of unique column pairs |
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Jenkins | 2008-01-12 03:06:20 | Re: SQL question: Highest column value of unique column pairs |
Previous Message | Kevin Jenkins | 2008-01-12 00:10:19 | SQL question: Highest column value of unique column pairs |