Select top three results from different catagories, I cannot seem to get this to work. I have a table full of hockey team Options

[yennhi01011991]

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]

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]

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...