Printable Version of Topic

Click here to view this topic in its original format

HTMLHelp Forums _ Databases _ Select top three results from different catagories

Posted by: yennhi01011991 Nov 13 2008, 12:53 AM

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!

Posted by: Brian Chandler Nov 13 2008, 01:14 AM

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

Posted by: Remote DBA Jan 23 2009, 08:51 AM

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)