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.