Suppose you’re trying to query column, but you need to set a minimum value to be returned, even if the column contains values that are less than that minimum. For example, you want a minimum value of 50 to be returned, even if the column contains values that are less than 50.

We can use the GREATEST() function to build such a query.

Example

Suppose we have the following table:

SELECT * FROM Sales;

Result:

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

And suppose we want to guarantee a minimum commission of 50 to each seller. So they will receive a minimum commission of 50 even if they earn less than that amount.

We can use the GREATEST() function to help us achieve this:

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 earn less, their “total commission” is calculated to be 50. But if they earn more than 50 they still get the higher amount.