MySQL Orderby a number Nulls last

Question

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

User · Answer

You can coalesce your NULLs in the ORDER BY statement    select   from tablename where  lt conditions gt  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

User · Answer

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

User · Answer

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

User · Answer

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 position ASC   A good reference is here http   troels arvin dk db rdbms select-order by

User · Answer

Why don t you order by NULLS LAST   SELECT    FROM tablename WHERE visible   1  ORDER BY position ASC NULLS LAST  id DESC

User · Answer

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

User · Answer

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

User · Answer

NULL LAST  SELECT   FROM table name ORDER BY id IS NULL  id ASC

User · Answer

SELECT   FROM tablename WHERE visible 1 ORDER BY CASE WHEN  position    0 THEN  a  END   position ASC

User · Answer

I found this to be a good solution for the most part   SELECT   FROM table ORDER BY ISNULL field   field ASC

User · Answer

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

User · Answer

This is working fine   SELECT   FROM tablename ORDER BY position   0  position ASC      position 1  2 3 0 0

[mysql] MySQL Orderby a number, Nulls last

Examples related to mysql

Examples related to sql-order-by