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 11:59:52 +1030
Message-ID: <47881810.7030303@Sheeky.Biz> (raw)
In-Reply-To: <4788056B.2020809@rakkar.org>
References: <4788056B.2020809@rakkar.org>
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
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: <47881810.7030303@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