If you have a query that returns multiple columns with numeric values in SQL Server, you can pass them to the GREATEST() function to find out which column has the highest value.

Example

Suppose we have a table like this:

SELECT * FROM Scores;

Result:

PlayerId    Game1       Game2       Game3      
----------- ----------- ----------- -----------
          1          20          30          15
          2          17          10          28
          3          47          23          16

And suppose we want to find out each player’s highest score, regardless of which game it was from. We can achieve this with the following:

SELECT 
  PlayerId,
  GREATEST( Game1, Game2, Game3 ) AS "Highest Score"
FROM Scores;

Result:

PlayerId    Highest Score
----------- -------------
          1            30
          2            28
          3            47

The Highest Score column returns the highest score for each player. So player 1’s highest score is 30, player 2’s is 28, and player 3 got a high score 47.

We can see by looking at the original table that these scores were taken from each of the three columns. Player 1’s high score was in the second game, player 2’s was in the third game, and player 3’s was in the first game.

Here we used the GREATEST() function to return the greatest value from the three given columns. The GREATEST() function accepts up to 254 arguments, and so if we had more than three columns we could simply include them in the list (as long as we don’t have more than 254 columns).

We could use the LEAST() function to do the opposite – get the lowest value.