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.
解决方案SELECT *
FROM tablename
WHERE visible=1
ORDER BY
case when position in('', '0') then 1 else 0 end,
position ASC,
id DESC