How to Order by a Number and Place NULLs Last in MySQL?

MySQL: Ordering by a Number and Nulls Last

In SQL queries, ordering data by a numeric field can be straightforward. However, managing null values in such scenarios requires careful consideration. In MySQL, by default, null values are treated as 0 when ordering with ASC or DESC, which can lead to undesired results.

To address this issue, MySQL offers an unconventional syntax to prioritize null values last in an ordering clause. By placing a minus sign (-) before the column name and inverting the order (ASC to DESC or vice versa), you can effectively reverse the sorting behavior for nulls.

For example, if you wish to sort a column named 'position' in ascending order (except for null values) and then break ties with 'id' in descending order, you would use the following syntax:

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

This ordering will result in the following sequence:

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

Essentially, this syntax inverts the sorting for the 'position' column, placing null values last while maintaining the desired ordering for non-null values.

Remember that this syntax is undocumented and may not be supported in all versions of MySQL. Additionally, it's always a good practice to carefully review the documentation before implementing any advanced sorting techniques in your queries.

The above is the detailed content of How to Order by a Number and Place NULLs Last in MySQL?. For more information, please follow other related articles on the PHP Chinese website!