The GREATEST() Function

tags:

The GREATEST() function returns the largest value from a list of expressions.

• It compares two or more expressions and returns the expression with the highest value.
• If any of the expressions are NULL, the result is NULL unless all the expressions are NULL, in which case the function returns NULL.

General Syntax

GREATEST(expr1, expr2, ..., expr_n)

Example: Consider a table sales with columns amount1, amount2, and amount3. You want to find the greatest value among these columns for each row.

sqlCopy code
SELECT
  sales_id,
  amount1,
  amount2,
  amount3,
  GREATEST(amount1, amount2, amount3) AS max_amount
FROM
  sales;

Using DECODE() With GREATEST()

The DECODE function in SQL provides conditional logic, similar to an IF-THEN-ELSE structure, while the GREATEST function returns the largest value from a list of expressions.

Combining DECODE with GREATEST allows you to conditionally transform or categorize data based on the maximum value among several columns or expressions.

Example: Assigning a level based on the maximum value

SELECT employee_id,
       salary,
       bonus,
       commission,
       DECODE(GREATEST(salary, bonus, commission),
              salary, 'Top Salary Earner',
              bonus, 'Top Bonus Earner',
              commission, 'Top Commission Earner',
              'No Top Earner') AS earning_status
FROM employees;