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 NULL entries for 'position' are treated as 0. Therefore all entries with position as NULL appear before those with 1,2,3,4. eg:
NULL, NULL, NULL, 1, 2, 3, 4
Is there a way to achieve the following ordering:
1, 2, 3, 4, NULL, NULL, NULL.
Best Answer
MySQL has an undocumented syntax to sort nulls last. Place a minus sign (-) before the column name and switch the ASC to DESC:
It is essentially the inverse of
position DESC
placing the NULL values last but otherwise the same asposition ASC
.A good reference is here http://troels.arvin.dk/db/rdbms#select-order_by