MySQL Orderby a number, Empty Strings (or 0's) Last

mysql, sql-order-by

Solution

SELECT * 
FROM tablename 
WHERE visible=1 
ORDER BY 
    case when position in('', '0') then 1 else 0 end,
    position ASC, 
    id DESC

Problem

Just asked a question pretty similar to this one... Currently I am doing a very basic OrderBy in my statement. ``` SELECT * FROM tablename WHERE visible=1 ORDER BY position ASC, id DESC ``` The problem with this is that empty string entries for 'position' are treated as 0. Therefore all entries with position as empty string appear before those with 1,2,3,4. eg: ``` '', '', '', 1, 2, 3, 4 ``` or: ``` 0, 0, 0, 1, 2, 3, 4 ``` Is there a way to achieve the following ordering: ``` 1, 2, 3, 4, '', '', ''. ``` or: ``` 1, 2, 3, 4, 0, 0, 0. ``` I assume the solution may have some kind of replace function but I am not able to find a function which does what I am after.

Original source