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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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?
Related posts: