Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql order by, null first, and DESC after

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 
like image 722
Ervin Avatar asked Feb 16 '12 08:02

Ervin


People also ask

How do you ORDER BY NULL?

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.

When data is sorted in ascending order NULL values appear first in the list?

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 in the ORDER BY clause?

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.

What is the default sorting order in SQL NULL?

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.


2 Answers

Try this:

ORDER BY [reuestId] IS NULL DESC, [reuestId] DESC 

should work (for mySql)

like image 165
DonCallisto Avatar answered Sep 22 '22 06:09

DonCallisto


SELECT * FROM TableX ORDER BY (requestId IS NOT NULL)        , requestId DESC 
like image 36
ypercubeᵀᴹ Avatar answered Sep 22 '22 06:09

ypercubeᵀᴹ