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 189 · Accepted Answer · answered Feb 16 '12 at 08:13

189

Try this:

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

should work (for mySql)

answered Feb 16 '12 at 08:13

DonCallisto

29,419
9
72
100

Or subquery to category – Feb 04 '16 at 15:03

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

34,845
8
116
142

answered Feb 16 '12 at 08:13

ypercubeᵀᴹ

113,259
19
174
235

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

@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

score 0 · Answer 3 · answered Oct 12 '22 at 13:44

0

A shorter way: SELECT * FROM table ORDER BY -column

This will sort rows ascending order but the values negated, where top rows are rows with null value.

answered Oct 12 '22 at 13:44

MobCode100

166
1
5

score 0 · Answer 4 · answered May 18 '23 at 15:13

0

I know the op is tagged with mysql, but if anyone got here looking for a solution for SQLServer, where is seems is null is not supported in the order by clause, use the following:

ORDER BY iif(requestId IS NULL,1,0)
       , requestId DESC

answered May 18 '23 at 15:13

GilShalit

6,175
9
47
68

mysql order by, null first, and DESC after

4 Answers4

Linked

Related