CODEWITHSUNDEEP
mysqlsql
Morten
Morten

Reputation: 378

What is the best way to get sorted SQL query based on null values

I have a table in my database which i want to extract data in a specific order.
Here is an example of the table:

CREATE TABLE `test` (
  `field1` int(11) NOT NULL,
  `field2` int(11) DEFAULT NULL,
  `field3` int(11) DEFAULT NULL,
  UNIQUE KEY `field1_2` (`field1`,`field2`),
  UNIQUE KEY `field1_3` (`field1`,`field3`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Valid entries in my table is:

The order i want to extract the data is:

So far i have used three queries connected with UNION

SELECT * FROM test WHERE field2 IS NULL 
    AND field3 IS NULL UNION
SELECT * FROM test WHERE field2 IS NULL 
    AND field3 IS NOT NULL UNION 
SELECT * FROM test WHERE field2 IS NOT NULL 
    AND field3 IS NULL;

I myself feel this is to much necessary code to get it in order and hoped there was a better solution.

So is there a better way?

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Just put your conditions into a single order by:

order by (field2 is null and field3 is null) desc,
         (field2 is null and field3 is not null) desc,
         (field2 is null and field3 is not null) desc

You might want to add a where as well:

where field2 is null or field3 is null

This works because MySQL treats booleans as numbers in a numeric context, with "true" as 1 and "false" as 0. This is why the desc is needed. You can express the same thing in standard SQL as:

order by (case when field2 is null and field3 is null then 1
               when field2 is null and field3 is not null then 2
               when field2 is not null and field3 is null then 3
               else 4
          end)

Upvotes: 1

Related Questions