MySQL Orderby a number, Nulls last

Question

Currently I am doing a very basic OrderBy in my statement.

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

The problem with this is that NULL entries for 'position' are treated as 0. Therefore all entries with position as NULL appear before those with 1,2,3,4. eg:

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

Is there a way to achieve the following ordering:

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

You should reconsider user1052645's answer. It's simpler, requires no knowledge of max values, and could be faster (assuming evaluating an expression may be faster than a function call). — Steve Clay, Dec 22 '11 at 16:36
The [accepted answer](https://stackoverflow.com/a/8174026/470530) is **WRONG**!!!!! See [here](https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=427a8a0dd63fe710ce949b03ae72ab69) and [here](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=30b5babbdecd20d7724c77fe2abc2056) - the `ISNULL (field), field ASC` works as does the `CASE` solution! — Vérace - Слава Україні, Dec 08 '20 at 21:07

score 631 · Accepted Answer · answered Nov 17 '11 at 20:43

631

MySQL has an undocumented syntax to sort nulls last. Place a minus sign (-) before the column name and switch the ASC to DESC:

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

It is essentially the inverse of position DESC placing the NULL values last but otherwise the same as position ASC.

A good reference is here http://troels.arvin.dk/db/rdbms#select-order_by

answered Nov 17 '11 at 20:43

Jarred

6,432
1
13
3

92

It's not undocumented, `- col_name` is an expression (`0 - col_name`), which the ORDER BY clause accepts. Of course this only works for numeric columns. – Steve Clay Dec 22 '11 at 16:31
7

Nice one. Works for `date` and `time` columns too! (MySQL 5.5). I guess (i am lazy to check) it works for all number-like columns (timestamp, float...). – Martin Aug 21 '12 at 16:14
1

Be careful as this is undocumented and may be removed in future revisions – koral Jul 11 '13 at 07:41
6

@koral: it is a simple (and useful) math expression that reverses the order, it won't be removed unless the language itself dramatically change. – Bell Aug 09 '13 at 19:29
12

As the comments suggest, it works for numeric, date and time columns? But, what about varchar? Can it be applied for varchar as well? I tried applied it to varchar fields, but the order seems to be different than from using either ASC or DESC. – Sumit Desai Feb 04 '14 at 07:41
2

Awesome , Most clever answer :) No extra load on poor mysql – vishal vasudeva Jun 20 '16 at 07:00
10

Won't this prevent the usage of an possible index on the order by column? – Tarsis Sep 22 '16 at 07:21
1

for varchar I used order by COALESCE(cn.our_name, cn.name,"яяяяяяяяяя") '; , if your language is English , replace яяяя with zzzzzz – Tebe Jul 21 '17 at 12:37
It works for dates, numeric, characters, ENUM, booleans,.. remember the minus `-` cast characters to 0, those come before 'null' when `ORDER BY DESC`. Notice though, for characters, that will leave you without any particular order for non null values. – user10089632 Jan 23 '18 at 08:42
this will only work for date time and number. alpha numeric and alphabetic will not work. – sas Jun 26 '18 at 11:31
Doesn't work on a float column, spits out values in what seems random order. – that-ben Feb 09 '19 at 15:59
To clarify you reverse the sort order, with NULLS at the end then just remove the negative sign and keep the sort order the same – Captain Fantastic May 22 '20 at 14:38
**Doesn't work!** See [here](https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=328268af28fc4a0bb1aebbccc87b292a) - you can see the first two `SELECT`s show that your solution (which I unfortunately upvoted) fails! – Vérace - Слава Україні Dec 08 '20 at 13:30
Just to pile on for anyone else on the fence about this answer: use `ORDER BY ISNULL(field)`. This answer has several significant drawbacks. Don't do it. – bsplosion Sep 02 '21 at 19:35

score 352 · Answer 2 · answered Nov 16 '10 at 14:42

352

I found this to be a good solution for the most part:

SELECT * FROM table ORDER BY ISNULL(field), field ASC;

answered Nov 16 '10 at 14:42

d-_-b

6,265
5
37
57

10

Without redefining order by works: ```SELECT * FROM table ORDER BY ISNULL(field) ASC;``` (MySQL 5.5) – Marçal Juan Sep 07 '12 at 11:10
1

Nice! I use this variation for empty fields: `SELECT *, position = '' as has_position FROM table ORDER BY has_position, position` – neokio Sep 13 '12 at 14:01
7

This is a better solution. – Rok Kralj Dec 09 '13 at 12:36
5

The accepted solution does not work with TIMESTAMP in postgresql 9.3. This solution does... – kalu Aug 18 '14 at 19:17
This can be used for text fields as well... nice. – Earth Engine Oct 02 '14 at 10:34
1

@MarçalJuan, What does your comment mean? – Pacerier Mar 10 '15 at 04:39
I think it's just the same query but shorter :) – Marçal Juan Mar 10 '15 at 11:17
To move empty and null fields to the end, I would use this query: `SELECT * FROM table ORDER BY ISNULL(field), (field = '');` – Abbas Sep 18 '15 at 18:13
3

Annoyingly, MySQL won't use an index on field when you add isnull(field) to the order by clause (when using limit). – Barry Kelly Oct 09 '15 at 14:06
5

@kalu: In **PostgreSQL**, NULL values are sorted *last* in ascending order (and first in descending order). And you would rather use the [*standard SQL* clause `NULLS LAST` | `NULLS FIRST`](http://stackoverflow.com/a/9511492/939860) to flip it instead of the workarounds here. – Erwin Brandstetter Jan 15 '16 at 13:40
@kalu Note that the question is tagged specifically as "mysql". – Kelvin Nov 11 '16 at 23:40
1

@BarryKelly You can add new virtual column `isNullField GENERATED ALWAYS AS ISNULL(field) VIRTUAL` add it to the index and use for ordering. VIRTUAL fieldss not use disk space for data, only for index. – Crusader Feb 15 '17 at 08:12
@Crusader a fine choice if you're working with 5.7.6 or above – Barry Kelly Feb 16 '17 at 10:51
That's nice solution – FosAvance Aug 02 '17 at 19:03
1

It sounds like @kalu may be hoping to use the same statements in MySQL and PostgreSQL...which would be unwise in any case – Andy Sep 18 '18 at 17:12
3

This should be the accepted answer. The -sort_column trick doesn't even work on all types of columns. This answer does work perfectly fine! Thanks – that-ben Feb 09 '19 at 16:08
1

This solution works with numbers and strings as well: it should be marked as best answer. The current best answer works only with numbers. – Dan Feb 11 '19 at 13:13
2021 checking in -- brilliant. Thank you. – John S Mar 18 '21 at 17:59

Sumeet · Answer 3 · 2016-12-20T08:52:37.420

30

NULL LAST

SELECT * FROM table_name ORDER BY id IS NULL, id ASC

edited Dec 20 '16 at 08:52

answered Dec 20 '16 at 07:50

Sumeet

1,553
18
26

1

nice working solution, can you explain it? – l2ysho Oct 26 '21 at 16:50

score 25 · Answer 4 · answered Jan 12 '10 at 19:13

25

Something like

SELECT * FROM tablename where visible=1 ORDER BY COALESCE(position, 999999999) ASC, id DESC

Replace 999999999 with what ever the max value for the field is

answered Jan 12 '10 at 19:13

DrewM

1,878
14
13

4

This solution is fragile and may lead to intermittent bugs – Dzmitry Bahdanovich Jan 14 '19 at 20:13

Langdon · Answer 5 · 2010-01-12T19:23:17.980

4

You can swap out instances of NULL with a different value to sort them first (like 0 or -1) or last (a large number or a letter)...

SELECT field1, IF(field2 IS NULL, 9999, field2) as ordered_field2
  FROM tablename
 WHERE visible = 1
 ORDER BY ordered_field2 ASC, id DESC

edited Jan 12 '10 at 19:23

answered Jan 12 '10 at 19:11

Langdon

19,585
18
86
106

This won't solve the problem as the index referenced in ORDER BY will not be affected by replacing values in the SELECT statement, and thereby won't correct the ordering. Also, check out the COALESCE function, which is functionally equivalent to your use of the IF function. – defines Jan 12 '10 at 19:16
If you alias the IF statement properly, the rows are ordered as you'd expect. I fixed my example. – Langdon Jan 12 '10 at 19:23

score 4 · Answer 6 · edited Feb 02 '16 at 15:49

4

Try using this query:

SELECT * FROM tablename
WHERE visible=1 
ORDER BY 
CASE WHEN position IS NULL THEN 1 ELSE 0 END ASC,id DESC

edited Feb 02 '16 at 15:49

Misa Lazovic

2,805
9
31
38

answered Feb 02 '16 at 11:17

Rachit Patel

824
5
12

No need for case. IS NULL returns 1 when expression is NULL. See reverbnation's answer. – contactmatt Aug 22 '18 at 01:10

score 3 · Answer 7 · answered Jan 12 '10 at 19:14

You can coalesce your NULLs in the ORDER BY statement:

select * from tablename
where <conditions>
order by
    coalesce(position, 0) ASC, 
    id DESC

If you want the NULLs to sort on the bottom, try coalesce(position, 100000). (Make the second number bigger than all of the other position's in the db.)

score 3 · Answer 8 · answered Aug 15 '16 at 22:33

3

For a DATE column you can use:

NULLS last:

ORDER BY IFNULL(`myDate`, '9999-12-31') ASC

Blanks last:

ORDER BY IF(`myDate` = '', '9999-12-31', `myDate`) ASC

answered Aug 15 '16 at 22:33

Danny Beckett

19,460
23
103
133

score 3 · Answer 9 · answered Sep 19 '17 at 11:29

3

SELECT * FROM tablename WHERE visible=1 ORDER BY CASE WHEN `position` = 0 THEN 'a' END , position ASC

answered Sep 19 '17 at 11:29

YasirPoongadan

563
4
17

7

Why should the OP ___try this___? **Good answers** will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer. – RiggsFolly Sep 19 '17 at 12:40

score 3 · Answer 10 · answered Sep 14 '19 at 09:54

To achieve following result :

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

USE syntax, place -(minus sign) before field name and use inverse order_type(Like: If you want order by ASC order then use DESC or if you want DESC order then use ASC)

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

score 3 · Answer 11 · answered May 21 '21 at 09:00

3

This works well for me as well.

ORDER BY ISNULL(field), field = 0 ASC;

Returns 1 2 3 0 0 null null

answered May 21 '21 at 09:00

jcdsr

982
1
14
29

score 2 · Answer 12 · edited Oct 16 '19 at 12:54

2

This is working fine:

SELECT * FROM tablename ORDER BY position = 0, position ASC;

position
1 
2
3
0
0

edited Oct 16 '19 at 12:54

Kos

4,483
8
35
39

answered Oct 16 '19 at 12:34

voxl.de

21
1

score 1 · Answer 13 · edited May 25 '21 at 13:53

1

That's simple. You just need to order twice:

first step, order NULLs
second step, order your field.

SELECT * FROM table_name 
ORDER BY ISNULL(field_name), field_name;

It works with any types, including JSON.

edited May 25 '21 at 13:53

Paul Roub

35,848
27
79
88

answered May 25 '21 at 13:49

Roman Samarsky

221
1
10

score -9 · Answer 14 · edited Dec 13 '11 at 15:05

-9

Why don't you order by NULLS LAST?

SELECT * 
FROM tablename
WHERE visible = 1 
ORDER BY position ASC NULLS LAST, id DESC

edited Dec 13 '11 at 15:05

Brandon

67,189
30
193
221

answered Dec 13 '11 at 15:04

RedRover

1
1

`NULLS LAST` - what version of MySQL was that introduced? – crmpicco May 15 '12 at 10:58
2

@Panique, You mean (MS) SQL Server? – d-_-b Jun 28 '12 at 09:29
1

this answer doesn't apply to MySQL – PeppyHeppy Mar 05 '13 at 02:53
this solution works for java db but not not for mysql – Lukas Apr 17 '21 at 14:14

MySQL Orderby a number, Nulls last

14 Answers14

Linked

Related