Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL special ORDER BY

Suppose I have the following table

name        | genre
---------------------
book 1      | scifi
book 2      | horror
book 3      | scifi
book 4      | romance
book 5      | horror

How can I sort the table above by "genre" in SQL to get the following results. Please note the order. It's H S R, and not H R S. Is it possible to achieve this without using UNION?

name        | genre
------------------------
book 2      | horror
book 5      | horror
book 1      | scifi
book 3      | scifi
book 4      | romance

Edit: There are only 3 genres.

like image 483
David Weng Avatar asked Dec 20 '22 20:12

David Weng


2 Answers

You could use the field function to customise the sort order

SELECT * FROM `table` ORDER BY FIELD(`genre`, 'horror', 'scifi', 'horror'), `name`;
like image 86
Ben Rowe Avatar answered Dec 30 '22 11:12

Ben Rowe


Try: select * from table order by case when genre="romance" then 1 else 0 end, genre;

like image 29
Jeshurun Avatar answered Dec 30 '22 10:12

Jeshurun