SQL question: Highest column value of unique column pairs

Поиск
Список
Период
Сортировка
От Kevin Jenkins
Тема SQL question: Highest column value of unique column pairs
Дата
Msg-id 4788056B.2020809@rakkar.org
обсуждение исходный текст
Ответы Re: SQL question: Highest column value of unique column pairs  (Shane Ambler <pgsql@Sheeky.Biz>)
Re: SQL question: Highest column value of unique column pairs  ("Marc Mamin" <M.Mamin@intershop.de>)
Список 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!


В списке pgsql-sql по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: (possible) bug with constraint exclusion
Следующее
От: Shane Ambler
Дата:
Сообщение: Re: SQL question: Highest column value of unique column pairs