Mysql Question posted in
The official MySQL documentation can be found here.

MySQL ORDER columns by empty last but without alphabetical order

I’m trying to order columns by empty string last. For example.

 NAME
--------------------------
 Apple
 ''
 Orange
 Avocado
 Banana
 Cauliflower
 Broccoli
 ''
 Potato
 Cabbage

The order should be like this without alphabetical order

     NAME
    --------------------------
     Apple     
     Orange
     Avocado
     Banana
     Cauliflower
     Broccoli   
     Potato
     Cabbage
     ''
     ''

but what I’m getting is

     NAME
    --------------------------
     Apple 
     Avocado     
     Banana
     Broccoli 
     Cauliflower
     Cabbage
     Orange
     Potato         
     ''
     ''

I tried many queries with if, case .etc but all of them were ordered alphabetically

some of the solutions I tried

SELECT * FROM `comments` order by  NULLIF(NULLIF(comment ,'0'),''),comment desc;

SELECT * FROM `comments` ORDER BY IF(comment IS NULL, 1,0), comment desc;

SELECT * FROM `comments` ORDER BY case when comment is null then 1 else 0 end,
    comment desc
Tags:

2

Answers


  1. 0

    Update:

    You can simply use (name ='') as order by clause which will return 1 if name is empty other wise 0.

    select * from comments order by (name ='')

    If you have null as well then you can use:

    select * from comments order by (NULLIF(name,'') is null)

    Here NULLIF(name,'') will convert empty string into null

    You can select rows without empty strings first without any order. Then you can select the rows with empty strings. You can combine both the results with union all

    Query:

    select * from comments where name <>”
    union all
    select * from comments where name =”

    Output:

    name
    Apple
    Orange
    Avocado
    Banana
    Cauliflower
    Broccoli
    Potato
    Cabbage

    fiddle

    Better approach would be to use conditional order clause like below:

    Query:

    select * from comments order by (case when name =” then 1 else 0 end)

    Output:

    name
    Apple
    Orange
    Avocado
    Banana
    Cauliflower
    Broccoli
    Potato
    Cabbage

    fiddle

    Login or Signup to reply.
  2. 0

    Have you tried checking if comment column is empty string in conjunction with checking for null?

    SELECT * FROM `comments` ORDER BY if(comment = '' or comment is null,1,0)

    or using trim method

    SELECT * FROM `comments` ORDER BY if(trim(comment) is null,1,0)

    Also I noticed in the table, you showed the column name is NAME instead of comment, just want to clarify if the column name is either NAME or comment? If it’s NAME, the query should reflect that

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search