MySQL: Order by field, placing empty cells at end

27,451

Solution 1

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

Solution 2

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 |
Share:
27,451

Related videos on Youtube

BenM
Author by

BenM

Founder of Kolekto, the collection inventory app. Code monkey, historian and WW2 US militaria collector! Working generally with Symfony, RedBean and React. Dabble in Slim and other microframeworks.

Updated on July 20, 2020

Comments

  • BenM
    BenM over 3 years

    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!

  • Ken White
    Ken White over 12 years
    @BenM: If @nick's answer solved your problem, you should accept it. @Nick: +1.
  • BenM
    BenM over 12 years
  • BenM
    BenM over 12 years
    @Ken White - I have to wait 2 minutes longer before I can accept Nick's answer...
  • Leo Galleguillos
    Leo Galleguillos about 9 years
    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.
  • ruhnet
    ruhnet over 4 years
    This works correctly and is the easiest way for NULL values, but doesn't work on empty string values.
  • Gavin
    Gavin over 2 years
    If you need to test for both NULL and an empty string, you can do ORDER BY name IS NOT TRUE, name DESC