MySQL Orderby a number, Nulls last

mysql sql-order-by
JonB picture JonB · Jan 12, 2010 · Viewed 125.5k times · Source

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.

Answer

Jarred picture Jarred · Nov 17, 2011

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

Related questions

How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by …

Read More
MySQL order by before group by

There are plenty of similar questions to be found on here but I don't think that any answer the question adequately. I'll continue from the current most popular question and use their example if that's alright. The task in this …

Read More
Order a MySQL table by two columns

How do I sort a MySQL table by two columns? What I want are articles sorted by highest ratings first, then most recent date. As an example, this would be a sample output (left # is the rating, then the article …

Read More