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

Python

Python

MySQL

MySQL

MySQL

Python

## Python read, validate and import CSV/JSON file to MySQL

You've successfully subscribed to SoftHints - Python, Data Science and Linux Tutorials
Great! Next, complete checkout for full access to SoftHints - Python, Data Science and Linux Tutorials
Welcome back! You've successfully signed in