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?
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