CODEWITHSUNDEEP
mysqlsorting
rolomcflurry
rolomcflurry

Reputation: 117

MySQL multiple sort and ignores?

Made a test sqlfiddle if anyone wants to attempt this...

http://sqlfiddle.com/#!9/904e18/2/0


In my database I have this: 

Group: A
Name: A-1, Points: 7pts
Name: A-2, Points: 6pts
Name: A-3, Points: 6pts
Name: A-4, Points: 5pts

Group: B
Name: B-1, Points: 1pts
Name: B-2, Points: 5pts
Name: B-3, Points: 4pts
Name: B-4, Points: 6pts

Group: C
Name: C-1, Points: 6pts
Name: C-2, Points: 8pts
Name: C-3, Points: 9pts
Name: C-4, Points: 2pts



I need to sort it first by the overall leader with the most points. (C-3)

Then I need to sort it by the second overall leader, but not in the group of the overall leader. (A-1)

Next, I need to sort it by the third overall leader, but not in the the group of the overall leader or second leader (B-4)

Then sort the rest by points.

So it should like this:

C-3
A-1
B-4
^^The leaders of each group, sorted by highest to lowest
-----
Then the remaining ones by points highest to lowest, no group specificity.

Upvotes: 0

Views: 42

Answers (1)

Dimos
Dimos

Reputation: 8918

You can get the top-3 teams in the way you want with the following query:

select * from teams
where points = (select max(points) from teams as t where t.`group` = teams.`group`)
order by points desc

You can also get the rest of the teams in the way you want with the following query : 

select *  from teams
where name not in 
  (select name from teams
  where points = (select max(points) from teams as t where t.`group` = teams.`group`))
order by points desc

The problem is that if you union those 2 queries, then you will lose any ordering they contain. So, in order to combine those 2 results, you will have to add a sortKey in the 2 results and use it in the ordering of the final result, in order to retain the ordering. The query is the following : 

(select *, 1 as SortKey from teams
where points = (select max(points) from teams as t where t.`group` = teams.`group`))

UNION ALL

(select *, 2 as SortKey from teams
where name not in 
  (select name from teams
  where points = (select max(points) from teams as t where t.`group` = teams.`group`)))

order by SortKey, points desc

You can also see it in this sqlfiddle

Upvotes: 1

Related Questions