skyline — 2012-11-28T07:55:08-05:00 — #1
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...
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?
skyline — 2012-11-28T08:00:30-05:00 — #2
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
guelphdad — 2012-11-28T10:07:40-05:00 — #3
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.
FIELD (rating, 'first', 'second', 'third'),
skyline — 2012-11-28T10:46:18-05:00 — #4
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!)
r937 — 2012-11-28T12:19:56-05:00 — #5
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 --
BY CASE WHEN rating = 'first' THEN 1
WHEN rating = 'second' THEN 2
WHEN rating = 'third' THEN 3
ELSE 937 END
, rating_count DESC