If you want to find the highest or the lowest value in MySQL with single query then you can use functions like:
- max
- min
- greatest
- least
Depending on your data you may need to do some tricks in order to manage null values properly. Find some useful examples below:
Highest value from several columns
If you have table with three columns (with numbers or dates) and you need to greatest date or number from the three then you can do it by:
| id | quater1 | quater2 | quater3 | quater4 |
|---|---|---|---|---|
| 1 | 67 | 57 | 88 | 31 |
| 2 | 22 | 34 | 66 | 74 |
| 3 | 0 | 3 | 32 | 51 |
| 4 | 56 | 34 | 9 | 34 |
| 5 | 67 | 65 | 34 | 65 |
| 6 | 6 | 3 | 9 | 6 |
| 7 | 3 | 45 | 45 | 75 |
| 8 | 89 | 78 | 9 | 45 |
SELECT GREATEST(resmax.max1, resmax.max2, resmax.max3)
FROM
(
SELECT MAX(quater1) AS max1, MAX(quater2) AS max2, MAX(quater3) AS max3
FROM score) resmax
result:
89
The lowest value from several columns
In similar way you can find the lowest value from several columns:
SELECT LEAST(resmin.min1, resmin.min2, resmin.min3, resmin.min4)
FROM
(
SELECT MIN(quater1) AS min1, MIN(quater2) AS min2, MIN(quater3) AS min3, MIN(quater4) AS min4
FROM score) resmin
result:
0
Finding the earliest date from several columns
The query logic is similar and will be done by using function least and a subquery:
SELECT LEAST(resmin.min1, resmin.min2, ..., resmin.minN)
FROM (
SELECT MIN(`d1`) AS min1, MIN(`d2`) AS min2,..., MIN(`dN`) AS minN
FROM mytable
) AS resmin
Finding max value in columns with null values
By using function COALESCE we can replace all null(or any other values) cells by a default value: 0, '2000-01-01' etc. If a column is not allowing null we can skipped COALESCE. In this example the query will find the earliest date per row:
| 2018-02-21 | 2018-03-01 | 2018-02-26 |
| 2018-02-23 | 2018-03-02 | 2018-02-25 |
| 2018-02-23 | 2018-02-28 | 2018-02-27 |
SELECT
LEAST(d1,
COALESCE(d2, 0),
COALESCE(d3, 0)) as mindate
FROM mytable
result:
| 2018-02-21 |
| 2018-02-23 |
| 2018-02-23 |
If we need the ealiest date for the three columns then we can use(COALESCE is not needed):
SELECT LEAST(resmin.min1, resmin.min2, resmin.min3)
FROM (
SELECT MIN(`d1`) AS min1, MIN(`d2`) AS min2,MIN(`dN`) AS minN
FROM mytable
) AS resmin
result
2018-02-21
Table definition
Table used in this examples:
CREATE TABLE `score` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`quater1` INT(7) NULL DEFAULT NULL,
`quater2` INT(7) NULL DEFAULT NULL,
`quater3` INT(7) NULL DEFAULT NULL,
`quater4` INT(7) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=11
;
INSERT INTO `test`.`score` (`id`, `quater1`, `quater2`,`quater3`, `quater4`) VALUES( 1, 67, 57, 88, 31);
INSERT INTO `test`.`score` (`id`, `quater1`, `quater2`,`quater3`, `quater4`) VALUES( 2, 22, 34, 66, 74);
INSERT INTO `test`.`score` (`id`, `quater1`, `quater2`,`quater3`, `quater4`) VALUES( 3, 0, 3, 32, 51);
INSERT INTO `test`.`score` (`id`, `quater1`, `quater2`,`quater3`, `quater4`) VALUES( 4, 56, 34, 9, 34);
INSERT INTO `test`.`score` (`id`, `quater1`, `quater2`,`quater3`, `quater4`) VALUES( 5, 67, 65, 34, 65);
INSERT INTO `test`.`score` (`id`, `quater1`, `quater2`,`quater3`, `quater4`) VALUES( 6, 6, 3, 9, 6);
INSERT INTO `test`.`score` (`id`, `quater1`, `quater2`,`quater3`, `quater4`) VALUES( 7, 3, 45, 45, 75);
INSERT INTO `test`.`score` (`id`, `quater1`, `quater2`,`quater3`, `quater4`) VALUES( 8, 89, 78, 9, 45);
Example 2
CREATE TABLE `mytable` (
`d1` DATE NULL DEFAULT NULL,
`d2` DATE NULL DEFAULT NULL,
`d3` DATE NULL DEFAULT NULL
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;
INSERT INTO `mytable` VALUES ('2018-02-21', '2018-03-01', '2018-02-26');
INSERT INTO `mytable` VALUES ('2018-02-23', '2018-03-02', '2018-02-25');
INSERT INTO `mytable` VALUES ('2018-02-23', '2018-02-28', '2018-02-27');