Using case in mysql ORDER BY

Question

I have a table consisting of: elements, ranking ranking and the number of votes that the user imposed on it. For simplicity: id, ranks, voices.

I am trying to run a query that sorts items from the highest score to the lowest if the number of votes = 0.

Since I have a base ranking score for new items, ordering my rating is not possible.

I 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 by looking: 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 perfect way out:

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

As you can see, this does not group all 0 voted items to the end.

I am sure that this forehead is respectfully simple, I would really appreciate any pointers and best practices. Hooray!

Answer 1

QUERY

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

What the ORDER BY section says:

• for a SELECT query, when votes = 0 (the element has no votes), then mySQL will consider the number of votes equal to 0.
• ELSE - for all other vote numbers, consider the number of votes = 1.
• END - completes the "case"
• DESC - Order our newly reviewed votes (0 or 1) from highest to lowest. those. group 1 at the top.
• DESC rating - a condition of secondary ordering within the main condition of ordering, from highest to lowest.

Answer 2

This group is all 0 to the end.

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

Answer 3

I think you do not need a simple case, do the job

 SELECT * FROM ranks ORDER BY Votes DESC,Rating desc 

Demo