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

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:
2010-2011 Standings by Conference - NHL.com - Standings