SQL: Select Rows in Descending Order by Column2 and Only Show 1 Result When Column1 is not NULL

Question

I have a table:

Id -- Column1 -- Column2 -- ....

I want to select all rows in descending order by Column2 where Id = 1234 and Column1 is NULL:

SELECT * 
FROM my_table 
WHERE Id = 1234 AND Column1 is NULL 
ORDER BY column2 DESC 

PLUS

1 rows with largest value in Column2 where Id = 1234 and Column1 is not NULL:

SELECT * 
FROM my_table 
WHERE Id = 1234 AND Column1 is NOT NULL 
ORDER BY column2 DESC 
LIMIT 1

How to combine those two SQL queries into one?

Answer 1

If you use UNION ALL statement you can merge two different query result.

ALL option in UNION statement doesn't remove duplicated row. This, because you'll get the maximum value of column2 in the first query too.

Try this:

SELECT t.* FROM 
(SELECT id, column1, column2 
FROM my_table 
WHERE Id = 1234 AND Column1 is NULL 
UNION ALL
(SELECT id, column1, column2 
FROM my_table 
WHERE Id = 1234 AND Column1 is NOT NULL 
ORDER BY column2 DESC 
LIMIT 1)) as t
ORDER BY t.column2 DESC 

Sql Fiddle

Answer 2

UNION Syntax:

UNION is used to combine the result from multiple SELECT statements into a single result set.

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

Let's build out a demo table for what you are trying to accomplish:

-- ----------------------------
-- Table structure for my_table
-- ----------------------------
DROP TABLE IF EXISTS `my_table`;
CREATE TABLE `my_table` (
  `Id` int(11) NOT NULL,
  `Column1` varchar(255) DEFAULT NULL,
  `column2` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of my_table
-- ----------------------------
INSERT INTO `my_table` VALUES ('1234', '5', '6');
INSERT INTO `my_table` VALUES ('1234', '6', '2');
INSERT INTO `my_table` VALUES ('1234', '7', '0');
INSERT INTO `my_table` VALUES ('1234', '8', '21');
INSERT INTO `my_table` VALUES ('1234', '9', '4');
INSERT INTO `my_table` VALUES ('1234', '12', '3');
INSERT INTO `my_table` VALUES ('1234', null, '8');
INSERT INTO `my_table` VALUES ('1234', null, '22');
INSERT INTO `my_table` VALUES ('1234', null, '19');
INSERT INTO `my_table` VALUES ('1234', null, '0');
INSERT INTO `my_table` VALUES ('1235', '15', '2');
INSERT INTO `my_table` VALUES ('1235', '8', '5');
INSERT INTO `my_table` VALUES ('1235', '7', '100');
INSERT INTO `my_table` VALUES ('1235', '2', '7');
INSERT INTO `my_table` VALUES ('1235', null, '1');
INSERT INTO `my_table` VALUES ('1235', null, '0');

We can now combine your two queries using UNION and dropping the ORDER BY in your statements as we're going to apply that to the UNION. Notice that I've used MAX() to determine the maximum value for column2 where Column1 is not NULL. This is what we get:

(SELECT * FROM `my_table` WHERE `Id` = '1234' AND `Column1` IS NULL)
UNION
(SELECT `Id`, `Column1`, MAX(`column2`) FROM `my_table` WHERE `Id` = '1234' AND `Column1` IS NOT NULL)
ORDER BY `column2` DESC

Which give us the following result:

+------+---------+---------+
| Id   | Column1 | column2 |
+------+---------+---------+
| 1234 | NULL    |      22 |
| 1234 | 5       |      21 |
| 1234 | NULL    |      19 |
| 1234 | NULL    |       8 |
| 1234 | NULL    |       0 |
+------+---------+---------+