CODEWITHSUNDEEP
sqlmysqldatabase
Roalt
Roalt

Reputation: 8440

Reorder integer except for value 0 with sql

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

Upvotes: 6

Views: 1045

Answers (3)

True Soft
True Soft

Reputation: 8796

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

is a solution without CASE

Upvotes: 5

mjv
mjv

Reputation: 75275

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

Upvotes: 2

davek
davek

Reputation: 22925

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

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

Upvotes: 6

Related Questions