MySQL: Order by field, placing empty cells at end

Question

I have a MySQL table which contains a number of products. What I want to do is sort the table by one particular column (most of the values begin with numbers for example: 1st, 2nd), etc. However, since some records do not have a value for this column, when I try to sort, the table automatically puts empty rows FIRST.

I am looking for a way to sort the row ASCENDING, but only insert blank records at the end of the sorted records, if that makes sense?

Any help would be most gratefully received!

score 95 · Accepted Answer · answered Apr 28 '11 at 22:36

95

select * from table
order by if(field = '' or field is null,1,0),field

answered Apr 28 '11 at 22:36

Nicola Cossu

54,599
15
92
98

@BenM: If @nick's answer solved your problem, you should accept it. @Nick: +1. – Ken White Apr 28 '11 at 22:42
Another quick question for you if I could? http://stackoverflow.com/questions/5825875/mysql-sort-1st-before-101st – BenM Apr 28 '11 at 22:44
1

@Ken White - I have to wait 2 minutes longer before I can accept Nick's answer... – BenM Apr 28 '11 at 22:45
10

A bit simpler: `ORDER BY IF (\`field\` <> '', 0, 1)` ASC; Also, be careful when using a function in the `ORDER BY` clause; MySQL cannot use an index in these cases. These queries will work fine on "small" tables but will slow down drastically as your project scales in size. – Leo Galleguillos Sep 07 '14 at 22:04

score 15 · Answer 2 · answered Jul 19 '18 at 11:10

15

This is one of the most effective method

ASC Order

SELECT * FROM user ORDER BY name IS NULL, name ASC

Expected Result:

+----+--------+------------+
| id |  name  | date_login |
+----+--------+------------+
|  3 |  david | 2016-12-24 |
|  2 |  john  | NULL       |
|  4 |  zayne | 2017-03-02 |
|  1 |  NULL  | 2017-03-12 |

DESC Order

SELECT * FROM user ORDER BY name IS NULL, name DESC

Expected Result:

+----+--------+------------+
| id |  name  | date_login |
+----+--------+------------+
|  4 |  zayne | 2017-03-02 |
|  2 |  john  | NULL       |
|  3 |  david | 2016-12-24 |
|  1 |  NULL  | 2017-03-12 |

answered Jul 19 '18 at 11:10

Ahmad Vaqas Khan

628
8
9

This works correctly and is the easiest way for NULL values, but doesn't work on empty string values. – ruhnet Aug 05 '19 at 18:27
2

If you need to test for both NULL and an empty string, you can do `ORDER BY name IS NOT TRUE, name DESC` – Gavin Apr 29 '21 at 14:58

MySQL: Order by field, placing empty cells at end

2 Answers2

Linked