Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 4C1042E2DA8 for ; Fri, 11 Jan 2008 23:06:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 95843-08 for ; Fri, 11 Jan 2008 23:06:15 -0400 (AST) Received: from dukecmmtar01.coxmail.com (dukecmmtar01.coxmail.com [68.99.120.48]) by postgresql.org (Postfix) with ESMTP id 0AF012E2D97 for ; Fri, 11 Jan 2008 23:06:23 -0400 (AST) Received: from [192.168.1.2] (really [70.169.225.166]) by dukecmmtar01.coxmail.com (InterMail vM.6.01.06.01 201-2131-130-101-20060113) with ESMTP id <20080112030623.FZGV26830.dukecmmtar01.coxmail.com@[192.168.1.2]> for ; Fri, 11 Jan 2008 22:06:23 -0500 Message-ID: <47882EAC.4030203@rakkar.org> Date: Fri, 11 Jan 2008 19:06:20 -0800 From: Kevin Jenkins User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 CC: pgsql-sql@postgresql.org Subject: Re: SQL question: Highest column value of unique column pairs References: <4788056B.2020809@rakkar.org> <47881810.7030303@Sheeky.Biz> In-Reply-To: <47881810.7030303@Sheeky.Biz> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/99 X-Sequence-Number: 29952 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 > > > > >