It's important to understand the differences between MySQL MAX function and GREATEST function.

• MAX function compares rows within a column to find the maximum value. It compares vertically.
• MAX function requires the use of GROUP BY clause.
• GREATEST function compares data across a list of arguments (values and/or columns). It compares horizontally.
• GREATEST function does not require the use of GROUP BY clause.

When using GREATEST function, please consider the following rules:

1. It returns NULL if any argument is NULL.
2. If the return value is used in an INTEGER context or all arguments are integer-valued, they are compared as integers.
3. If the return value is used in a REAL/FLOAT context or all arguments are real-valued, they are compared as reals.
4. If the arguments comprise a mix of numbers and strings, they are compared as strings.
5. If any argument is a case-sensitive string, the arguments are compared as case-sensitive strings.
6. In all other cases, the arguments are compared as case-insensitive strings.
7. The arguments can have mixed data types. It can mix actual values and column names of a table.
8. If two columns contain the same largest values, the GREATEST function returns the value in the first encountered column based on the order of columns in table structure.

Syntax:

GREATEST(value1, value2, value3, ...)

or

GREATEST(column1, column2, column3, ...)

or

GREATEST(value1, value2, value3, column1, column2, column3, ...)

As shown above, the arguments can mix actual values and column names of a table.

Create the sample table for this tutorial

To demonstrate how the GREATEST function works, we need to create a table called quarterly_sales which contains quarterly sales amounts for each product in 1997 in Northwind database. We'll use this table to create our tutorial queries.

create table quarterly_sales 
as
select a.ProductName, 
    round(sum(case quarter(c.OrderDate) when '1' 
        then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end)) as Qtr1,
    round(sum(case quarter(c.OrderDate) when '2' 
        then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end)) as Qtr2,
    round(sum(case quarter(c.OrderDate) when '3' 
        then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end)) as Qtr3,
    round(sum(case quarter(c.OrderDate) when '4' 
        then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end)) as Qtr4 
from Products a 
inner join Order_Details b on a.ProductID = b.ProductID
inner join Orders c on c.OrderID = b.OrderID
inner join Customers d on d.CustomerID = c.CustomerID 
where c.OrderDate between date('1997-01-01') and date('1997-12-31')
group by a.ProductName
order by a.ProductName, d.CompanyName;

The table created contains 77 products with Quarter 1 to Quarter 4 sales figures.

Practice 1:

The query below returns the largest value in the arguments based on what the context the list is in.

SELECT GREATEST(2, 5, 0) AS integer_context,
       GREATEST(2, 5, 0, null) AS integer_context,
       GREATEST(34.0, 3.0, 5.0, 767.0) AS real_context,
       GREATEST('B', 'a', 'c', 'd') AS case_insensitive_context,
       GREATEST('B', 'a', 5, 7, 'c', 'd') AS mixed_context;

1 row returned:

Practice 2: Case sensitive string comparison.

The two queries below instruct MySQL to perform case-sensitive string comparison by using COLLATE operator with utf8mb4_0900_as_cs or utf8mb4_bin collation.

Please note: utf8mb4_bin and utf8mb4_0900_as_cs have the same effect - bin is short for binary and _cs stands for case-sensitive.

SELECT GREATEST('B', 'b', 'c', 'C') COLLATE utf8mb4_bin AS case_sensitive_context;

SELECT GREATEST('B', 'b', 'c', 'C') COLLATE utf8mb4_0900_as_cs AS case_sensitive_context;

1 row returned:

Practice 3: Compare data across multiple columns

This query compares sales figures across the four quarters for each product and return the maximum sales amount.

select ProductName, 
       GREATEST(qtr1, qtr2, qtr3, qtr4) as Max_quarterly_sales 
from quarterly_sales;

77 rows returned:

Practice 4: Compare data across multiple columns and indicate which column has the smallest value

This query does the same thing as the query above, but in addition, it also works out which quarter has the smallest sales.

Please note that if two columns contain the same smallest values, the GREATEST function returns the value in the first encountered column based on the order of columns in table structure. For example, product Chef Anton's Gumbo Mix has both 0 sales in Quarter 1 and Quarter 2, but Quarter 1 is returned in the result (see Max_Quarter column).

select ProductName, 
	   GREATEST(qtr1, qtr2, qtr3, qtr4) as Max_quarterly_sales,
	   case when GREATEST(qtr1, qtr2, qtr3, qtr4)=qtr1 then 'Qtr 1'
                when GREATEST(qtr1, qtr2, qtr3, qtr4)=qtr2 then 'Qtr 2'
                when GREATEST(qtr1, qtr2, qtr3, qtr4)=qtr3 then 'Qtr 3'
       else 'Qtr 4'
       end as Max_Quarter
from quarterly_sales;

77 rows returned:

Happy Coding!

Other tutorials in this category

1. Using String Functions, Part 1

2. Using String Functions, Part 2

3. Using String Functions, Part 3

4. Using MySQL Date and Time Functions, Part 1

5. Using MySQL Date and Time Functions, Part 2

6. Using MySQL Date and Time Functions, Part 3

7. Using MySQL Control Flow Functions

8. Using MySQL Conversion Functions

9. Using MySQL Comparison Functions

10. Using Aggregate Functions in MySQL

11. MySQL RANK() function

12. MySQL DENSE_RANK() function

13. MySQL ROW_NUMBER() function

14. Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function

15. Using LIMIT and OFFSET to Page Thru Query Result in MySQL

16. Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL

17. Use LEAST function to find the SMALLEST value from multiple arguments

18. How to make case-sensitive comparison in MySQL

19. MySQL MID() function with examples

20. MySQL LOCATE() function with examples

21. MySQL SUBSTR() function with examples

22. MySQL POSITION() function with examples

23. How to use MySQL REPLACE function in SELECT and UPDATE queries

Back to Tutorial Index Page