MySQL Ordering a query - further question

Question

Hi all,

Further to a recently answered question, 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 IF(q21coding = 'Other', 1, 0) ASC, 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 now at the bottom - However is there a way of ensuring that Positive comments and Other is ALWAYS the bottom two (with other at the bottom) regardless of the Count size?

Thanks,

Homer

Answer 1

Actually there was no need to use IF(q21coding = 'Other', 1, 0) in your original query. 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.

What you need to do to have 'Positive Comments' and 'Other' both at the bottom is something like this:

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

Basic test case:

CREATE TABLE my_table (id int, q21coding varchar(100), count int);

INSERT INTO my_table VALUES (1, 'Inappropriate content/use', 13);
INSERT INTO my_table VALUES (2, 'Other', 30);
INSERT INTO my_table VALUES (3, 'Difficulty in navigating/finding content', 53);
INSERT INTO my_table VALUES (4, 'Positive comments', 28);
INSERT INTO my_table VALUES (5, 'Improve search facility', 6);
INSERT INTO my_table VALUES (6, 'Content needs updating', 4);
INSERT INTO my_table VALUES (7, 'Suggestions for improvement', 14);
INSERT INTO my_table VALUES (8, 'Include information about staff and teams', 5);

Result:

SELECT    q21coding, count
FROM      my_table
ORDER BY  q21coding = 'Other', q21coding = 'Positive comments', Count DESC;

+-------------------------------------------+-------+
| q21coding                                 | count |
+-------------------------------------------+-------+
| Difficulty in navigating/finding content  |    53 |
| Suggestions for improvement               |    14 |
| Inappropriate content/use                 |    13 |
| Improve search facility                   |     6 |
| Include information about staff and teams |     5 |
| Content needs updating                    |     4 |
| Positive comments                         |    28 |
| Other                                     |    30 |
+-------------------------------------------+-------+
8 rows in set (0.00 sec)