pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL question: Highest column value of unique column pairs
5+ messages / 3 participants
[nested] [flat]
* SQL question: Highest column value of unique column pairs
@ 2008-01-12 00:10 Kevin Jenkins <gameprogrammer@rakkar.org>
0 siblings, 2 replies; 5+ messages in thread
From: Kevin Jenkins @ 2008-01-12 00:10 UTC (permalink / raw)
To: pgsql-sql
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL question: Highest column value of unique column pairs
@ 2008-01-12 01:29 Shane Ambler <pgsql@Sheeky.Biz>
parent: Kevin Jenkins <gameprogrammer@rakkar.org>
1 sibling, 1 reply; 5+ messages in thread
From: Shane Ambler @ 2008-01-12 01:29 UTC (permalink / raw)
To: Kevin Jenkins <gameprogrammer@rakkar.org>; +Cc: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL question: Highest column value of unique column pairs
@ 2008-01-12 03:06 Kevin Jenkins <gameprogrammer@rakkar.org>
parent: Shane Ambler <pgsql@Sheeky.Biz>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Jenkins @ 2008-01-12 03:06 UTC (permalink / raw)
To: ; +Cc: 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
>
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL question: Highest column value of unique column pairs
@ 2008-01-12 10:30 Shane Ambler <pgsql@Sheeky.Biz>
parent: Kevin Jenkins <gameprogrammer@rakkar.org>
0 siblings, 0 replies; 5+ messages in thread
From: Shane Ambler @ 2008-01-12 10:30 UTC (permalink / raw)
To: Kevin Jenkins <gameprogrammer@rakkar.org>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL question: Highest column value of unique column pairs
@ 2008-01-14 08:39 Marc Mamin <M.Mamin@intershop.de>
parent: Kevin Jenkins <gameprogrammer@rakkar.org>
1 sibling, 0 replies; 5+ messages in thread
From: Marc Mamin @ 2008-01-14 08:39 UTC (permalink / raw)
To: Kevin Jenkins <gameprogrammer@rakkar.org>; pgsql-sql
Hello Kevin,
I would use "select distinct on" to first isolate the candidates in (1)
and (2) and then reitere the query on this sub result:
(the query below will retrieve the last score, not the best one...)
something like (not tested):
select distinct on (date,name)
date,name,score
from
(select distinct (on date, LName1)
date,LName1 as name ,score1 as score
from table
order by date desc,LName1
union all
select distinct on (date, LName2)
date,LName2 as name,score2 as score
from table
order by date desc, LName2
)foo
order by date desc,name
regards,
Marc Mamin
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Kevin Jenkins
Sent: Saturday, January 12, 2008 1:10 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] SQL question: Highest column value of unique column pairs
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!
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-01-14 08:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-12 00:10 SQL question: Highest column value of unique column pairs Kevin Jenkins <gameprogrammer@rakkar.org>
2008-01-12 01:29 ` Shane Ambler <pgsql@Sheeky.Biz>
2008-01-12 03:06 ` Kevin Jenkins <gameprogrammer@rakkar.org>
2008-01-12 10:30 ` Shane Ambler <pgsql@Sheeky.Biz>
2008-01-14 08:39 ` Marc Mamin <M.Mamin@intershop.de>
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