MySQL ORDER BY two clauses (descending and ascending)

I have this table:

create table products(name text, quantity int);
insert into products values ("A", 10);
insert into products values ("B", 0);
insert into products values ("C", 15);
insert into products values ("D", 0);
insert into products values ("E", 17);

I would like to order by product name, but keep products with quantity = 0 at the bottom, like this:

name    quantity
A       10
C       15
E       17
B       0
D       0

I’ve tried: select * from products order by quantity desc, name asc; but it but while it correctly keeps quantity = 0 at the bottom, name ordering is reversed. Is ORDER BY even the right thing to use in this case?

SQL Fiddle: http://sqlfiddle.com/#!9/6985a4/1/0

Answer

I would use:

order by (quantity = 0), name

Boolean values are ordered with “false” first then “true”.

I would use this to be explicit, rather than using a special value such as 'z' — which won’t even work if you have names that start with 'z'.

Advertisement

Answer