Pushing a row to the end of a sort with MySQL

Sometimes you have weird requirements to meet. It's a fact of life.

One of these requirements I came across recently was outputting a result set in alpha order, but with a specific tuple at the end, instead of where it belongs in the sort.

I decided I'd figure out how to do this in SQL instead of gross rendering or a re-sort.

Please note there are other ways to solve this that are less brittle, but this was in a fairly static data set for a one off solution.

Consider this table:

CREATE TABLE `animals` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(64)  NOT NULL,
  `slug` varchar(64)  NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_name`(`name`(64)),
  UNIQUE KEY `uniq_slug`(`slug`(64))
)
ENGINE = InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `animals` ( `id`, `name`, `slug` ) VALUES
  ( NULL, 'Dog', 'dog' ),
  ( NULL, 'Cat', 'cat' ),
  ( NULL, 'Other', 'other' ),
  ( NULL, 'Zebra', 'zebra' );

Now, pull them out in alpha order:

mysql> SELECT * 
    -> FROM `animals` 
    -> ORDER BY slug ASC;
+----+-------+-------+
| id | name  | slug  |
+----+-------+-------+
|  2 | Cat   | cat   |
|  1 | Dog   | dog   |
|  3 | Other | other |
|  4 | Zebra | zebra |
+----+-------+-------+
4 rows in set (0.00 sec)

But what if we always want "Other" at the end?

Well, our unique indices allow us to guarantee a conditional sort on the slug (or name).

To do this, we will use the CASE statement.

mysql> SELECT * 
    -> FROM `animals` 
    -> ORDER BY CASE 
    -> WHEN `slug` = 'other' 
    -> THEN 0 
    -> ELSE 1 
    -> END DESC, `slug` ASC;
+----+-------+-------+
| id | name  | slug  |
+----+-------+-------+
|  2 | Cat   | cat   |
|  1 | Dog   | dog   |
|  4 | Zebra | zebra |
|  3 | Other | other |
+----+-------+-------+
4 rows in set (0.00 sec)

Here we have two ORDER BY clauses. We first use CASE to return 1 whenever the slug is "other", and sort on that. Then we sort the sub-groups ("other" and "non-other") by slug.

CASE is handy for other stuff too, and can handle complex expressions.

mysql> SELECT *, CASE 
    -> WHEN `slug` IN ( 'cat', 'dog' ) 
    -> THEN 'yes' 
    -> ELSE 'no' 
    -> END AS 'is_good_pet' 
    -> FROM `animals`;
+----+-------+-------+-------------+
| id | name  | slug  | is_good_pet |
+----+-------+-------+-------------+
|  1 | Dog   | dog   | yes         |
|  2 | Cat   | cat   | yes         |
|  3 | Other | other | no          |
|  4 | Zebra | zebra | no          |
+----+-------+-------+-------------+
4 rows in set (0.00 sec)

Do you ever use CASE?