How to Get the Max Value using SQL

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.