SQL Server MAX() Function

Summary: in this tutorial, you will learn how to use the SQL Server MAX() function to find the maximum value in a set.

Introduction to the SQL Server MAX() function

In SQL Server, the MAX() function is an aggregate function that returns the maximum value in a set.

Here’s the syntax of the MAX() function:

MAX(expression)Code language: SQL (Structured Query Language) (sql)

The MAX() function accepts an expression that can be a column or a valid expression.

Similar to the MIN() function, the MAX() function ignores NULL and considers all values including duplicates.

SQL Server MAX() function examples

We will use the products and brands tables for the demonstration:

1) Basic SQL Server MAX() function example

The following statement uses the MAX() function to find the highest list price of all products in the products table:

SELECT
    MAX(list_price) max_list_price
FROM
    production.products;Code language: SQL (Structured Query Language) (sql)

Output:

max_list_price
--------------
11999.99Code language: CSS (css)

The following example uses the MAX() function in a subquery to find the highest list price and outer query to retrieve the products with the highest prices:

SELECT
  product_id,
  product_name,
  list_price
FROM
  production.products
WHERE
  list_price = (
    SELECT
      MAX(list_price)
    FROM
      production.products
  );Code language: SQL (Structured Query Language) (sql)

Output:

product_id | product_name                  | list_price
-----------+-------------------------------+-----------
155        | Trek Domane SLR 9 Disc - 2018 | 11999.99
(1 row)

In this example:

• First, use the MAX() function in the subquery to return the highest list price of all products.
• Then, retrieve the products whose list prices equal the highest price returned from the subquery in the outer query.

2) Using the MAX() with GROUP BY clause example

The following statement uses the MAX() function with the GROUP BY clause to retrieve the brand names and the highest list price for each brand:

SELECT
  brand_name,
  MAX(list_price) max_list_price
FROM
  production.products p
  INNER JOIN production.brands b ON b.brand_id = p.brand_id
GROUP BY
  brand_name
ORDER BY
  brand_name;Code language: SQL (Structured Query Language) (sql)

Output:

In this example:

• First, divide the products into groups by the brand names using the GROUP BY clause.
• Then, apply the MAX() function to each group to get the highest list price for each brand name.

3) Using the MAX() with HAVING clause example

The following example retrieves brand names and their corresponding highest list prices and filters out brands with the highest list prices less than or equal to 1000:

SELECT
    brand_name,
    MAX(list_price) max_list_price
FROM
    production.products p
    INNER JOIN production.brands b
        ON b.brand_id = p.brand_id 
GROUP BY
    brand_name
HAVING 
    MAX(list_price) > 1000
ORDER BY
    max_list_price DESC;Code language: SQL (Structured Query Language) (sql)

Output:

brand_name   | max_list_price
-------------+--------------
Trek         | 11999.99
Electra      | 2999.99
Heller       | 2599.00
Surly        | 2499.99
Sun Bicycles | 1559.99
Haro         | 1469.99
(6 rows)

4) Using the MAX() function with date columns

The following example uses the MAX() function to find the orders with the latest required date:

SELECT
  MAX(required_date) latest_required_date
FROM
  sales.orders;Code language: SQL (Structured Query Language) (sql)

Output:

latest_required_date
--------------------
2018-12-28

The following example uses the MAX() function with the GROUP BY clause to find the latest required date of all orders grouped by staff names:

SELECT
  s.first_name,
  MAX(required_date) latest_required_date
FROM
  sales.orders o
  INNER JOIN sales.staffs s ON s.staff_id = o.order_id
GROUP BY
  s.first_name
ORDER BY
  latest_required_date;Code language: SQL (Structured Query Language) (sql)

Output:

first_name | latest_required_date
-----------+---------------------
Fabiola    | 2016-01-03
Mireya     | 2016-01-04
Virgie     | 2016-01-04
Genna      | 2016-01-05
Kali       | 2016-01-05
Jannette   | 2016-01-06
Bernardine | 2016-01-06
Marcelene  | 2016-01-07
Venita     | 2016-01-07
Layla      | 2016-01-08
(10 rows)

5) Using the MAX() function with text column

The following statement uses the MAX() function to return the last product names sorted alphabetically within each category:

SELECT
  c.category_name   category_name,
  MIN(product_name) product_name
FROM
  production.products p
  INNER JOIN production.categories c ON c.category_id = p.category_id
GROUP BY
  c.category_name;Code language: SQL (Structured Query Language) (sql)

Output:

category_name       | product_name
--------------------+------------------------------------------------
Children Bicycles   | Electra Cruiser 1 (24-Inch) - 2016
Comfort Bicycles    | Electra Townie Balloon 3i EQ - 2017/2018
Cruisers Bicycles   | Electra Amsterdam Fashion 3i Ladies' - 2017/2018
Cyclocross Bicycles | Surly Straggler - 2016
Electric Bikes      | Electra Loft Go! 8i - 2018
Mountain Bikes      | Haro Flightline One ST - 2017
Road Bikes          | Surly ECR - 2018
(7 rows)

Summary

• Use the MAX() function to find the maximum value in a set of values.
• The MAX() function ignores NULL.