We all know with MySQL you can use ORDER BY with a list of columns to return an ordered set, e.g. ORDER BY name, type, state;
I often use the syntax ORDER BY 1,2; which I’m surprised that some people do not know about.
However I needed to do some selective ordering of a type field, I didn’t want to for example have a lookup table just to join for ordering. While contemplating a means of achieving this, I asked a work colleague, who I figured may have just experienced this problem before. Lone behold I became the student as I discovered there is a third syntax with ORDER BY, using expressions.
mysql> create table test(name varchar(10) not null, type varchar(10) not null);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into test(name,type) values
('Apples','Fruit'),
('Bananas','Fruit'),
('Carrots','Veg'),
('Onions','Veg'),
('Beer','Liquid'),
('Water','Liquid'),
('Crackers','Food');
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select name from test
order by type='Veg' DESC,
type='Fruit' DESC,
type='Food' DESC,
type='Liquid' DESC;
+----------+
| name |
+----------+
| Carrots |
| Onions |
| Apples |
| Bananas |
| Crackers |
| Beer |
| Water |
+----------+
7 rows in set (0.00 sec)
Of course, reading the MySQL Manual confirms this on the SELECT command
.
I’ve not read the MySQL manual from cover to cover, since 4.x days. Perhaps it’s time.
Thanks to Nick Pisarro of Blog Revolution for this most valuable tip.
