The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Select top three results from different catagories, I cannot seem to get this to work. I have a table full of hockey team
yennhi01011991
post 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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Nov 13 2008, 01:14 AM
Post #2


Jocular coder
********

Group: Members
Posts: 2,242
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


--------------------
Brian Chandler
Nothing in this post constitutes "commercial solicitation". PayPal does not solicit residents of Japan. Contents may settle in transit. "Legal mind" may or may not be brain-damaged.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Remote DBA
post 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...


--------------------
Thank you, Vadym.
MCITP DBA 2005/2008 Chief MS SQL DBA
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 24th October 2014 - 07:45 AM