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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With