Order by field and then count

I have some rows that are ordered by a count DESC. That’s fine, but I want to “pin” some specified fields to the top of the results. This field is empty for most results, but there are a few that have the date: first, second, third…

eg.

ORDER by FIELD (rating, ‘first’, ‘second’, ‘third’), count DESC

This doesn’t work! How can I get the results to show the ‘first’, ‘second’, ‘third’ first and then order the rest by count DESC?

I have tried:

ORDER by FIELD (rating, ‘first’, ‘second’, ‘third’) ASC, count DESC

This pins them to the top but in reverse order eg. third,second,first, but then does correctly order the remainder by the count

Your error is because of your use of COUNT. COUNT is a function so you have to give it something to count. Also the ASC isn’t necessary in the ORDER BY FIELD as you specify within which order you want.


ORDER BY
FIELD (rating, 'first', 'second', 'third'), 
count(*) DESC

Sorry my fault, it’s actually a variable called review_count which I have created earlier in the full query!
Weirdly, if I reverse the order of the fields, I get the order I need?! (ie third, second, first gives me the desired result!)

this is why it doesn’t work, because when the rating isn’t ‘first’ or ‘second’ or ‘third’ then the FIELD function returns 0, and of course 0 sorts ahead of 1, 2, and 3

make sense now?

workaround would be a CASE expression –

ORDER
    BY CASE WHEN rating = 'first' THEN 1
            WHEN rating = 'second' THEN 2
            WHEN rating = 'third' THEN 3
            ELSE 937 END
    , rating_count DESC