The introduction of the LEAST() and GREATEST() functions in SQL Server 2022 were a welcome addition. These functions enable us to get the minimum or maximum value from a list of values. There are plenty of use cases for these functions.

One such use case is to provide a cap on the values returned by a query.

Example

Suppose we have the following table:

SellerId    Commission 
----------- -----------
          1          25
          2         125
          3          77
          4          33
          5         180
          6          33

Now suppose we want to cap the commission at 100. In other words, sellers can only get a maximum of 100 in commissions. If they earn more than that, their commission will remain at 100 regardless of how much more they earn.

The LEAST() function can help us achieve this:

SELECT
  SellerId,
  Commission,
  LEAST(Commission, 100) AS "Capped Commission"
FROM Sales;

Result:

SellerId    Commission  Capped Commission
----------- ----------- -----------------
          1          25                25
          2         125               100
          3          77                77
          4          33                33
          5         180               100
          6          33                33

We can see that the “capped” commission goes no higher than 100. Any commission that’s lower than 100 remains at its original amount, but as soon as it surpasses 100, it remains fixed at 100.

We can do the opposite effect by swapping LEAST() with GREATEST(). For example, if all sellers are guaranteed a minimum commission of say, 50, we could do the following:

SELECT
  SellerId,
  Commission AS "Earned Commission",
  GREATEST(Commission, 50) AS "Total Commission"
FROM Sales;

Result:

SellerId    Earned Commission Total Commission
----------- ----------------- ----------------
          1                25               50
          2               125              125
          3                77               77
          4                33               50
          5               180              180
          6                33               50

This time all sellers have a total commission of no less than 50. If they earned less, their “total commission” is calculated to be 50.