CODEWITHSUNDEEP
mysqlsqlnullisnullsql-order-by
wishie
wishie

Reputation: 45

How to treat 'empty',0,NULL as the same in MySQL ORDER BY statement

I am using a database that I have no real control over (3rd party system) and one of the tables allows the 'featured' column to be either EMPTY,0,1, or NULL.

Any of the states EMPTY,0,NULL mean the same thing.

I ORDER BY the 'featured' column, then ORDER BY the 'name' column, but the resulting data is not in alphabetical order, except grouped by 'featured' in alphabetical lists.

FEATURED   |          NAME
--------------------------
1          | Apple
1          | Orange
0          | Avocado
0          | Banana
0          | Cauliflower
NULL       | Broccoli
NULL       | Potato
           | Cabbage

Basically, I need to have 0, NULL, EMPTY treated as the same value, so they are all ordered alphabetically.

Example of ideal output

FEATURED   |          NAME
--------------------------
1          | Apple
1          | Orange
0          | Avocado
0          | Banana
NULL       | Broccoli
           | Cabbage
0          | Cauliflower
NULL       | Potato

Upvotes: 2

Views: 2303

Answers (2)

spencer7593
spencer7593

Reputation: 108410

To get '0' or '' or NULL values to be sorted together, I would use an expression that "converts" two of those values into the third, so that the same value is returned in all three cases.

It appears that the featured column is character type, so I'd avoid unnecessary implicit datatype conversions, and my preference would be a fairly short expression.

To get the NULL and '0' converted to a '', so the rows were sorted first (in ascending sequence), I'd do something like:

  ORDER BY IFNULL(NULLIF(featured,'0'),''), name

If I wanted to return a NULL as the common value (so those rows got sorted last in ascending sequence), I'd convert the '' and '0' into NULL, something like this:

  ORDER BY NULLIF(NULLIF(featured,'0'),''), name

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269923

If you want them last, I would do this with an expression like this:

order by (featured is null or featured = '0' or featured = ''),
         name

You could simplify this to:

order by ((featured > 0) and featured is not null) desc, name

If you like.

Upvotes: 1

Related Questions