MySQL How to find highest and lowest value

John D K -

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');