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!
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...
Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)