mysql order by empty_MySQL Orderby一个数字，空字符串（或0），最后一个

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