How to get the highest and lowest value in a row in SQL

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 GREATEST() and 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, GREATEST() and LOWEST() return NULL is one of the arguments is NULL.

We can ignore NULL values by using IFNULL():

SELECT
    player_id,
    GREATEST(
        IFNULL(football_score, 0),
        IFNULL(golf_score, 0)
    ) AS best_sport_score
    FROM player;