How to Sort MySQL Results with NULL Values Last?

How to Order MySQL Results with Null Values Last

When ordering MySQL results with a numeric column, null values can be problematic as they are treated as zero. This can lead to unexpected ordering, where null values appear before non-null values.

To address this issue, MySQL offers an undocumented syntax that allows you to sort null values last. This is achieved by placing a minus sign (-) before the column name and switching the ASC to DESC:

SELECT * FROM tablename WHERE visible=1 ORDER BY -position DESC, id DESC

This syntax essentially inverts the position DESC ordering, placing null values last while maintaining the same ordering for non-null values.

For example, if you have the following data:

NULL, NULL, NULL, 1, 2, 3, 4

Using the standard ORDER BY position ASC, id DESC statement would result in:

NULL, NULL, NULL, 1, 2, 3, 4

However, using the -position DESC syntax would produce the desired ordering:

1, 2, 3, 4, NULL, NULL, NULL

This undocumented syntax provides a convenient way to ensure that null values are sorted last in MySQL results. It is important to note that this is not a standard SQL feature and may not be supported in all database systems.

The above is the detailed content of How to Sort MySQL Results with NULL Values Last?. For more information, please follow other related articles on the PHP Chinese website!