Select top three results from different catagories, I cannot seem to get this to work. I have a table full of hockey team |
Select top three results from different catagories, I cannot seem to get this to work. I have a table full of hockey team |
yennhi01011991 |
Nov 13 2008, 12:53 AM
Post
#1
|
Group: Banned Posts: 4 Joined: 14-October 08 Member No.: 6,897 |
I post detail at [spam link removed by moderator]
I cannot seem to get this to work. I have a table full of hockey teams records that I need to get the top three teams based on points but each team of the top three has to be from a different division. Here is an example: Table: Teams TEAM | POINTS | DIVISION T#1 25 DIV#1 T#2 23 DIV#1 T#3 30 DIV#2 T#4 12 DIV#2 T#5 26 DIV#3 T#6 27 DIV#3 The result should look like this: 1. T#3 2. T#6 3. T#1 Not look like this: 1. T#3 2. T#6 3. T#5 Anybody have an idea what a select statement would look like for this? Thanks is advance! This post has been edited by Christian J: Nov 19 2008, 08:41 AM |
Brian Chandler |
Nov 13 2008, 01:14 AM
Post
#2
|
Jocular coder Group: Members Posts: 2,460 Joined: 31-August 06 Member No.: 43 |
QUOTE I cannot seem to get this to work. I have a table full of hockey teams records that I need to get the top three teams based on points but each team of the top three has to be from a different division. Partly your problem may be because you have not expressed the question clearly. You are not looking for the top three teams at all, you are looking for the top team from each of three divisions. An SQL guru could probably help you directly, but if I were faced with this I'd simply write for($division=1; $division<=3; $division++) { _dbcall_("SELECT team FROM results WHERE division=$division ORDER BY score DESC LIMIT 1"); } where _dbcall_ is pseudocode for whatever you want to do with the SQL result. HTH This post has been edited by Brian Chandler: Nov 13 2008, 01:15 AM |
Remote DBA |
Jan 23 2009, 08:51 AM
Post
#3
|
Group: Members Posts: 8 Joined: 23-January 09 Member No.: 7,617 |
Hi yennhi01011991
Here you go select team from ( select division, max(t.score) as scoremax from ( select 'T#1'as team, 25 as score, 'DIV#1' as division union all select 'T#2'as team, 23 as score, 'DIV#1' as division union all select 'T#3'as team, 30 as score, 'DIV#2' as division union all select 'T#4'as team, 12 as score, 'DIV#2' as division union all select 'T#5'as team, 26 as score, 'DIV#3' as division union all select 'T#6'as team, 27 as score, 'DIV#3' as division ) t group by division ) groupped inner join ( select 'T#1'as team, 25 as score, 'DIV#1' as division union all select 'T#2'as team, 23 as score, 'DIV#1' as division union all select 'T#3'as team, 30 as score, 'DIV#2' as division union all select 'T#4'as team, 12 as score, 'DIV#2' as division union all select 'T#5'as team, 26 as score, 'DIV#3' as division union all select 'T#6'as team, 27 as score, 'DIV#3' as division ) teams on teams.score = groupped.scoremax and teams.division = groupped.division order by scoremax desc limit 0, 3 though the performance of query will be low... |
Lo-Fi Version | Time is now: 25th April 2024 - 07:07 AM |