How Can I Sort MySQL Data with NULL Values at the End?

MySQL NULL Filtering and Sorting

When ordering data in MySQL using ORDER BY, null values are treated as 0, causing inconsistencies in desired ordering. To address this issue, consider using a special syntax to force nulls to the end of the sorted list.

To sort values in ascending order and place nulls at the end, use the following syntax:

ORDER BY -position DESC

This reverses the default ASC ordering for the position column, effectively treating null values as the highest value. By combining this with a DESC ordering for the id column, you can achieve the desired ordering:

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

This will result in the following ordering:

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

Remember that this syntax is undocumented in MySQL and may not be available in all versions. For a comprehensive reference, refer to the MySQL documentation on ORDER BY with additional flags.

The above is the detailed content of How Can I Sort MySQL Data with NULL Values at the End?. For more information, please follow other related articles on the PHP Chinese website!