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.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
MySQL has an undocumented syntax to sort nulls last. Place a minus sign (-) before the column name and switch the ASC to DESC:
SELECT * FROM tablename WHERE visible=1 ORDER BY -position DESC, id DESC
It is essentially the inverse of
position DESC placing the NULL values last but otherwise the same as
A good reference is here http://troels.arvin.dk/db/rdbms#select-order_by
I found this to be a good solution for the most part:
SELECT * FROM table ORDER BY ISNULL(field), field ASC;
SELECT * FROM table_name ORDER BY id IS NULL, id ASC
SELECT * FROM tablename where visible=1 ORDER BY COALESCE(position, 999999999) ASC, id DESC
Replace 999999999 with what ever the max value for the field is
You can swap out instances of NULL with a different value to sort them first (like 0 or -1) or last (a large number or a letter)…
SELECT field1, IF(field2 IS NULL, 9999, field2) as ordered_field2 FROM tablename WHERE visible = 1 ORDER BY ordered_field2 ASC, id DESC
Try using this query:
SELECT * FROM tablename WHERE visible=1 ORDER BY CASE WHEN position IS NULL THEN 1 ELSE 0 END ASC,id DESC
You can coalesce your NULLs in the
ORDER BY statement:
select * from tablename where <conditions> order by coalesce(position, 0) ASC, id DESC
If you want the NULLs to sort on the bottom, try
coalesce(position, 100000). (Make the second number bigger than all of the other
position‘s in the db.)
DATE column you can use:
ORDER BY IFNULL(`myDate`, '9999-12-31') ASC
ORDER BY IF(`myDate` = '', '9999-12-31', `myDate`) ASC
SELECT * FROM tablename WHERE visible=1 ORDER BY CASE WHEN `position` = 0 THEN 'a' END , position ASC
To achieve following result :
1, 2, 3, 4, NULL, NULL, NULL.
USE syntax, place
-(minus sign) before field name and use inverse order_type(Like: If you want order by ASC order then use DESC or if you want DESC order then use ASC)
SELECT * FROM tablename WHERE visible=1 ORDER BY -position DESC
This works well for me as well.
ORDER BY ISNULL(field), field = 0 ASC;
This is working fine:
SELECT * FROM tablename ORDER BY position = 0, position ASC;
position 1 2 3 0 0
That’s simple. You just need to order twice:
- first step, order NULLs
- second step, order your field.
SELECT * FROM table_name ORDER BY ISNULL(field_name), field_name;
It works with any types, including JSON.
Why don’t you order by NULLS LAST?
SELECT * FROM tablename WHERE visible = 1 ORDER BY position ASC NULLS LAST, id DESC