It is well-known how to get the highest and lowest values in a table column, but it is a bit less known how to get the highest and lowest values in a row. Let’s see how to do it.
To get the highest and lowest values from the same row, we can use the
LEAST() functions, respectively. They take any number of arguments.
This is mainly useful in denormalised tables:
SELECT GREATEST(player1_score) AS player1_best_score, GREATEST(player2_score) AS player2_best_score, GREATEST(player3_score) AS player3_best_score FROM game;
Dealing with NULL
Like most functions,
NULL is one of the arguments is
We can ignore
NULL values by using
SELECT player_id, GREATEST( IFNULL(football_score, 0), IFNULL(golf_score, 0) ) AS best_sport_score FROM player;