Order by a particular condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order by a particular condition

Author

Topic

Aravinthan
Starting Member

3 Posts

Posted - 2011-06-11 : 11:53:11

Hey guys,

Ok so I have a code that allows me to order but it's not perfect and I am looking to improve on it.

Ok so first what I need to do: Its a standings Est/West like the NHL

There is 3 divisions per conference ( Est or West )
There is 4 teams per division.
The first 3 places of each conference ( Est or West ) is the first team per division.
And then, after the third place, its all the teams from the conference in order of points.
If 2 teams has the same number of points, you have to order by wins.
If 2 teams has teh same number of wins, you have to order by goals for.

Here is the code I have so far:


SELECT a.*, (CASE WHEN b.scoreMax IS NULL THEN 1 ELSE 2 END) AS SortFiddle
									FROM (SELECT teams.*, teams_numbers.Divison, ((`nhl_wins` *2) + `nhl_ot`) AS scoreMax
									FROM teams
									LEFT JOIN teams_numbers ON teams.team_name = teams_numbers.ProName
									WHERE teams_numbers.Conference ='Est'  ) a
									LEFT OUTER JOIN
									(SELECT Divison, MAX((`nhl_wins` *2) + `nhl_ot`) AS scoreMax
									FROM teams
									LEFT JOIN teams_numbers ON teams.team_name = teams_numbers.ProName
									WHERE teams_numbers.Conference ='Est'  GROUP BY Divison ORDER BY `nhl_wins`,scoreMax LIMIT 3) b
									ON a.Divison = b.Divison
									AND a.scoreMax = b.scoreMax
									ORDER BY SortFiddle DESC, a.scoreMax DESC, `nhl_wins` DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-12 : 04:18:32

would be much better if you can show by data sample what ordering you're looking at

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-12 : 07:35:01

And this is a MS SQL Server forum so you maybe can get better help in a MySQL forum...

No, you're never too old to Yak'n'Roll if you're too young to die.

Aravinthan
Starting Member

3 Posts

Posted - 2011-06-12 : 08:13:46

@webred,
Yeah sorry, I got mixed up.

And @sakh16,

I completly forgot to tell you guys about my DB structure.. Sorry about that.

Ok so I have 2 tables in use here:
1- teams_numbers
Contains:id,Pro_name,conference,division

2- teams
Contains: id,nhl_gp,nhl_wins,nhl_ot,team_name

So, We can link both tables using the id or team_name/Pro_name
To get the points for a team, we have to do nhl_wins*2 + nhl_ot

If you guys need an exemple:
http://www.nhl.com/ice/standings.htm?type=con#&navid=nav-stn-conf

Aravinthan
Starting Member

3 Posts

Posted - 2011-06-13 : 23:20:41

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources