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.
This question is related to
mysql
sql-order-by
For a DATE
column you can use:
NULLS last:
ORDER BY IFNULL(`myDate`, '9999-12-31') ASC
Blanks last:
ORDER BY IF(`myDate` = '', '9999-12-31', `myDate`) ASC
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
Why don't you order by NULLS LAST?
SELECT *
FROM tablename
WHERE visible = 1
ORDER BY position ASC NULLS LAST, id DESC
NULL LAST
SELECT * FROM table_name ORDER BY id IS NULL, id ASC
This is working fine:
SELECT * FROM tablename ORDER BY position = 0, position ASC;
position
1
2
3
0
0
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
SELECT * FROM tablename WHERE visible=1 ORDER BY CASE WHEN `position` = 0 THEN 'a' END , position ASC
I found this to be a good solution for the most part:
SELECT * FROM table ORDER BY ISNULL(field), field ASC;
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.)
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
Something like
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
Source: Stackoverflow.com