How to Get the Max Value using SQL

February 12, 2022

To find the max value using SQL:

SELECT 
MAX(field_name) AS new_field_name 
FROM table_name

Next, you’ll see how to find the max value across the following 3 scenarios:

  1. Under a single field
  2. After joining tables
  3. After using group by

(1) Find the Max Value Under a Single Field

To start with a simple example, let’s create a table called the ‘products‘ table:

CREATE TABLE products (
	product_id int primary key,
	product_name nvarchar(50),
	product_price int
)

And then insert 5 records into the table:

INSERT INTO products (product_id, product_name, product_price)

VALUES

(1, 'Laptop', 1200),
(2, 'Tablet', 300),
(3, 'Monitor', 500),
(4, 'Printer', 150),
(5, 'Keyboard', 80)

You can then find the max price by running the following query:

SELECT 
MAX(product_price) AS max_price
FROM products

The maximum price that you’ll get is 1200.

(2) Find the Max Value After Joining Tables

Let’s add a second table called the ‘brands‘ table:

CREATE TABLE brands (
	product_id int primary key,
	product_brand nvarchar(50)
)

And now insert 5 records into the table as follows:

INSERT INTO brands (product_id, product_brand)

VALUES

(1, 'AA'),
(2, 'BB'),
(3, 'AA'),
(4, 'BB'),
(5, 'BB')

The ‘prices’ table can then be joined to the ‘brands’ table using the product_id field (which can be found under both tables):

SELECT
pr.product_name,
pr.product_price,
br.product_brand
FROM products pr
LEFT JOIN brands br ON pr.product_id = br.product_id

Here are the results of the joined tables:

product_name product_price product_brand
Laptop 1200 AA
Tablet 300 BB
Monitor 500 AA
Printer 150 BB
Keyboard 80 BB

Suppose that you want to find the max price after joining the tables together (and display the complete record).

In that case, you’ll need to add the following WHERE clause at the bottom of the query:

WHERE pr.product_price = (SELECT MAX(product_price) FROM products)

So the full query would look as follows:

SELECT
pr.product_name,
pr.product_price,
br.product_brand
FROM products pr
LEFT JOIN brands br ON pr.product_id = br.product_id
WHERE pr.product_price = (SELECT MAX(product_price) FROM products)

You’ll then get the complete record where the max price is 1200:

product_name product_price product_brand
Laptop 1200 AA

(3) Apply a Group By

What if you’d like to find the max price per brand?

In that case, you may use MAX() and GROUP BY to achieve this goal:

SELECT
MAX(pr.product_price) AS max_price,
br.product_brand
FROM products pr
LEFT JOIN brands br ON pr.product_id = br.product_id
GROUP BY br.product_brand

You’ll now get the max price per brand:

max_price product_brand
1200 AA
300 BB

If you’d like to find the max price for a particular brand, such as the ‘BB’ brand, you may then specify the name of the brand in the WHERE clause:

WHERE br.product_brand = 'BB'

So the complete query to find the max price for the ‘BB” brand is:

SELECT
MAX(pr.product_price) AS max_price,
br.product_brand
FROM products pr
LEFT JOIN brands br ON pr.product_id = br.product_id
WHERE br.product_brand = 'BB'
GROUP BY br.product_brand

Therefore, the maximum price for BB is 300:

max_price product_brand
300 BB

Note that the WHERE clause needs to be placed before the GROUP BY.