SQL Server 2022 introduced the LEAST() function, which at first glance, may seem to do the same thing as the already existing MIN() function.

However, these are two separate functions with two separate purposes.

Definitions

First, let’s check the official definitions for each of these functions:

• LEAST(): Returns the minimum value from a list of one or more expressions.
• MIN(): Returns the minimum value in the expression. May be followed by the OVER clause.

I’ve bolded the key difference between these functions. The LEAST() function operates on one or more expressions, but the MIN() function operates on just a single expression.

Also, the MIN() function can have an OVER clause (allowing it to operate as a window function).

Example

Suppose we have the following table:

SELECT * FROM Scores;

Result:

PlayerId    Game1       Game2       Game3      
----------- ----------- ----------- -----------
          1          20          30          15
          2          40          19          30
          3          10          20          50

The LEAST() Function

We can use the LEAST() function against the above table like this:

SELECT 
  LEAST( Game1, Game2, Game3 ) AS "Lowest Score"
FROM Scores;

Result:

Lowest Score
------------
          15
          19
          10

Here, I passed three column names to the LEAST() function. It returned the lowest value from all three columns. It did this for each row in the table.

The MIN() Function

Here’s an example that uses the MIN() function against the same table:

SELECT 
  MIN(Game1) AS "Lowest Score from Game 1"
FROM Scores;

Result:

Lowest Score from Game 1
------------------------
                      10

Here, I used the MIN() function to return the lowest value from one specific column.

This function only accepts one argument, so we can’t pass three arguments like we did with the LEAST() function. If we try to do that we get an error:

SELECT 
  MIN(Game1, Game2, Game3) AS "Lowest Score"
FROM Scores;

Result:

Msg 174, Level 15, State 1, Server 39dddda85240, Line 16
The MIN function requires 1 argument(s).

However, we can include multiple MIN() calls in our SELECT list:

SELECT 
  MIN(Game1) AS "Game 1",
  MIN(Game2) AS "Game 2",
  MIN(Game3) AS "Game 3"
FROM Scores;

Result:

Game 1      Game 2      Game 3     
----------- ----------- -----------
         10          19          15

And we can use an OVER clause with MIN():

SELECT 
  PlayerId,
  MIN( Game1 ) OVER( ORDER BY PlayerId ) AS "Game 1",
  MIN( Game2 ) OVER( ORDER BY PlayerId ) AS "Game 2",
  MIN( Game3 ) OVER( ORDER BY PlayerId ) AS "Game 3"
FROM Scores;

Result:

PlayerId    Game 1      Game 2      Game 3     
----------- ----------- ----------- -----------
          1          20          30          15
          2          20          19          15
          3          10          19          15

In this case, the MIN() function was applied to each row, and its result reflected the minimum value until that point. So we can see that in Game 1, the first row is 20. That’s because the first player has a score of 20.

But we can see that the second row is also 20, even though that player got 40. The reason for this is that we’re using MIN() to return the minimum value until that point – a “running minimum” if you like.

And then we get to the third row. Suddenly the value drops to 10. That’s because the third player only got a score of 10. That’s the lowest score so far, and so that’s what’s returned by MIN().

We can see the same logic being applied to the other two columns.

Another thing we can do with the OVER clause is include a PARTITION BY sub-clause. This allows us to partition our results, thereby having the MIN() function operate within each partition (i.e. effectively resetting at the start of each partition).

So we can see that LEAST() and MIN() are two completely different functions, each with their own purpose. The function we choose will depend on what we’re trying to achieve with our query.