Reorder integer except for value 0 with sql

Question

I'm trying to get an ordered list of rows out of my MYSQL database table based upon an integer value 'place'.

SELECT * FROM mytable
ORDER BY place;

This works okay, except that all rows with value place=0 should appear at the end of the table.

So if my table is:

name place
---- -----
John 1
Do   2
Eric 0
Pete 2

it should become:

name place
---- -----
John 1
Do   2
Pete 2
Eric 0

Answer 1

order by case when place = 0 then 1 else 0 end asc, place asc

that way you get all the non-zeroes ordered first.

Answer 2

SELECT *
FROM myTable
ORDER BY CASE place WHEN 0 THEN 9999 ELSE place END

This approach implies that we known that 9999 (or some other value) is bigger than all possible values in the place column.

Alternatively we can sort by two values as in:

   ORDER BY CASE place WHEN 0 THEN 0 ELSE -1 END, place

Answer 3

SELECT *
FROM myTable
ORDER BY place>0 DESC, place

is a solution without CASE