Re: [SQL] SQL question: Highest column value of unique column pairs
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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, 120Jan 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] SQL question: Highest column value of unique column pairs
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, 120Jan 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SQL question: Highest column value of unique column pairs
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, 120Jan 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
Re: [SQL] SQL question: Highest column value of unique column pairs
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, 120Jan 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
