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.
Talking about expressions in ORDER BY, MySQL has a function that almost seems to exist for that very purpose, FIELD(). For instance, this query should return the same result as your example:
select name from test
order by FIELD(type, ‘Veg’, ‘Fruit’, ‘Food’, ‘Liquid’);
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_field
I suggest looking at using the field syntax:
mysql> SELECT name
-> FROM test
-> ORDER BY FIELD(type,’Veg’,’Fruit’,’Food’,’Liquid’);
+———-+
| name |
+———-+
| Carrots |
| Onions |
| Apples |
| Bananas |
| Crackers |
| Beer |
| Water |
+———-+
7 rows in set (0.00 sec)
It’s one of those things that I never would have thought of, but makes perfect sense and seems remarkably obvious now that I see it. After all, each of those expressions evaluates to a 1 or a 0, at which point it returns those that evaulate to 1 (true) first (when ordering descending).
Ronald, if you need a break from reading the MySQL manual, please also read this:
http://www.phrases.org.uk/meanings/lo-and-behold.html
Yeah, another neat syntax is
INSERT INTO tbl1 SET fld1=val1, SET fld2=val2, ….
which is much better than INSERT INTO tbl1 (fld1, fld2…) VALUES (val1, val2…) when there are lots of fields.
> I often use the syntax ORDER BY 1,2; which I’m surprised that some people do not know about.
I’m really happy that a lot of people don’t even know about it.
I think this is the worse syntax for the ORDER BY clause, especially for maintainability.
What happen during an upgrade from 4.x to 5.x where column order in a result set can change ? (the JOIN key appear twice in 4.x, once in 5.x)
++
Whether the original syntax or FIELD is used I think it is even more interesting with GROUP BY!
select
count(type) as ‘number of products’,
if(type=’Liquid’,’what you drink’,’everything else .. and less important’) as ‘what is this for’
from test
group by field(type,’Liquid’) desc;
The field() alternative as others have pointed out does look promising as well.
I teach students to recognise the ordinal position syntax (ORDER BY 1,2) but teach them to get rid of it.
I presume you picked up this syntax at Oracle, where you can’t have expressions or calculated fields in the ORDER BY.
Thus, SELECT foo * 2 AS bar FROM …. ORDER BY bar would not be allowed. So you use ORDER BY 1.
But it IS allowed in MySQL and then, it (IMHO) becomes sensible to get rid of the ordinal position stuff, as it keeps queries more maintainable and less error-prone. Otherwise, if someone changes the SELECT columns in some way, the ORDER BY can break without people realising.
That is nifty. My ungainly equivalent result using Oracle is
select name from
(
SELECT name,
(case
when type=’Veg’ then 1
when type=’Fruit’ then 2
when type=’Food’ then 3
when type=’Liquid’ then 4
else 5 end) sort_order
FROM TEST
)
order by sort_order, name
/
I like that field() function.
Here is another go from Oracle. Just working through this – do not mean to pollute. I found your blog as part of trying to learn about ec2.
SELECT name
FROM TEST
order by (case
when type=’Veg’ then 1
when type=’Fruit’ then 2
when type=’Food’ then 3
when type=’Liquid’ then 4
else 5 end), name
/
“Low and behold”
Oops, finger autocompletion:
“Lo and behold”
You can also use aggregate functions in ORDER BY, such as
select name from test order by length(name)
Hi,
Thanks for this Guide! I think it will help me out a lot.
But just while watching over this example (havnt tried it out myself yet) i think you may have a small mistake in your result set because your ordering by DESC and your results are listed in ASC order.
best regards
RB