Using case in mysql ORDER BY

Question

I have a table consisting of: items, a ranking score and the number of votes a user has cast on it. For simplicity: id, ranks, votes.

I'm trying to run a query which sorts items from highest ranking score to lowest, unless the number of votes = 0.

Because I have a base ranking score for new items, ordering my ranking isn't possible.

I've tried the following, along with some other permutations:

$list = $dbh->query('SELECT * FROM ranks ORDER BY votes desc, rating desc case when min(votes)= 0 else rating desc');

to no avail, after looking at : Conditional sorting in MySQL and Conditional sorting in MySQL?

I tried this:

SELECT * FROM ranks ORDER BY rating desc, case votes when 0 then votes end 

This is the table, and the output I get:

ID : Rating : Votes
2 : 201 : 9
3 : 100 : 0
4 : 100 : 0
5 : 100 : 0
1 : -13 : 9

My ideal output would be:

ID : Rating : Votes
2 : 201 : 9
1 : -13 : 9
4 : 100 : 0
5 : 100 : 0
3 : 100 : 0

As you can see, this doesn't group all the 0 voted items to the end.

I'm sure this is forehead slappingly simple, I'd really appreciate any pointers and best practices. Cheers!

Answer 1

This group all 0's to end.

SELECT * FROM `ranks` ORDER BY (Votes=0), Rating DESC

Answer 2

QUERY

SELECT * 
FROM ranks 
ORDER BY case votes 
WHEN 0 THEN 0 
ELSE 1 END DESC, rating DESC

What the ORDER BY section is saying is:

• for the SELECT query, when the votes = 0 (an item has no votes), then mySQL will consider the number of votes as 0.
• ELSE - for all other numbers of votes, consider the number of votes = 1.
• END - ends the 'case'
• DESC - order the our newly considered votes (0 or 1) from highest to lowest. i.e. group the 1's at the top.
• Rating DESC - the secondary ordering condition, within the primary ordering condition, order from highest rating to lowest.