mysql order by, null first, and DESC after

Question

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

score 181 · Accepted Answer · answered Feb 16 '12 at 08:13

181

Try this:

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

should work (for mySql)

answered Feb 16 '12 at 08:13

DonCallisto

score 33 · Answer 2 · edited Jan 21 '15 at 10:34

33

SELECT *
FROM TableX
ORDER BY (requestId IS NOT NULL)
       , requestId DESC

edited Jan 21 '15 at 10:34

mb21

answered Feb 16 '12 at 08:13

ypercubeᵀᴹ

I think this answer is opposite of the first one? – xi.lin Mar 05 '15 at 06:57
5

@xi.lin: No, the first one puts `IS NULL` *(which is essentially a function which returns a 1 or a 0)* in DESC order, this one puts `IS NOT NULL` in ascending order. They're equivalent. – BlueRaja - Danny Pflughoeft Apr 02 '15 at 03:52
@BlueRaja-DannyPflughoeft Thanks for the explain! I just wrongly thought IS NULL is a filter instead of a function. – xi.lin Apr 02 '15 at 04:40

2 Answers2