How can I order DESC by a field, but list the NULL values first?
So I'm having a table:
reuestId | offerId | offerTitle 1 | 1 | Alfa NULL | 2 | Beta 2 | 3 | Gamma
I want to select them so that the results would be:
NULL | 2 | Beta 2 | 3 | Gamma 1 | 1 | Alfa
SQL ORDER BY Clause Handling NULLS This means that if you specify a column in the ORDER BY clause that has null values in ascending order the NULL values will appear first in the result set. Conversely, if you specify descending order, they will appear last in the result set. Here is a simple example of each case.
SQL treats NULL values to be less than 0 so while sorting in ascending order, NULL values always appear to be at first.
How Are NULLs Sorted by Default? The SQL standard does not define the default ordering of NULLs. What does this mean? If you apply the ORDER BY clause to a column with NULLs, the NULL values will be placed either first or last in the result set.
The SQL standard does not explicitly define a default sort order for Nulls. Instead, on conforming systems, Nulls can be sorted before or after all data values by using the NULLS FIRST or NULLS LAST clauses of the ORDER BY list, respectively. This is the case for Oracle RDBMS.
Try this:
ORDER BY [reuestId] IS NULL DESC, [reuestId] DESC
should work (for mySql)
SELECT * FROM TableX ORDER BY (requestId IS NOT NULL) , requestId DESC
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With