MySQL Ordering Lists Question

Question

Hi All,

I have the following code:

SELECT    q21coding, COUNT(q21coding) AS Count 
FROM      `tresults_acme` 
WHERE     q21 IS NOT NULL AND q21 <> '' 
GROUP BY  q21coding 
ORDER BY  Count DESC

It brings back the following:

q21coding                                  Count 
Difficulty in navigating/finding content     53
Positive comments                            28
Suggestions for improvement                  14
Inappropriate content/use                    13
Improve search facility                       6
Include information about staff and teams     5
Content needs updating                        4
Other                                        30

You'll notice that Other is the second one down - is there a way of ensuring that Other is ALWAYS at the bottom regardless of the Count size?

Thanks,

Homer

Answer 1

Yes, add artificiall field which is 1 for "Other" and 0 for anything else. Then do "ORDER BY dummy, Count".

Answer 2

ORDER BY IF(q21coding = 'Other', 1, 0) ASC, Count DESC

Answer 3

@reko_t's answer is valid, but actually there is no need to use the IF() function.

In MySQL you can use any expression in the ORDER BY caluse and q21coding = 'Other' would have been enough:

... ORDER BY q21coding = 'Other', Count DESC

The q21coding = 'Other' expression will return 1 if true, or 0 if false. That will put rows with a q21coding = 'Other' at the bottom.