To find the max value using SQL:
SELECT
MAX(field_name) AS new_field_name
FROM table_name
3 Cases of Getting the Max Value
Case 1: Get the Max Value Under a Single Field
To start with a simple example, create a table called ‘products‘:
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.
Case 2: Get the Max Value After Joining Tables
Now add a second table called ‘brands‘:
CREATE TABLE brands (
product_id int primary key,
product_brand nvarchar(50)
)
And then insert 5 records into the table:
INSERT INTO brands (product_id, product_brand)
VALUES
(1, 'AA'),
(2, 'BB'),
(3, 'AA'),
(4, 'BB'),
(5, 'BB')
The ‘prices‘ table can now be joined with 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, 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 |
Case 3: Apply a Group By
What if you’d like to find the max price per brand?
In that case, 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 |
To find the max price for a particular brand, such as the ‘BB’ brand, 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
The maximum price for BB is 300:
| max_price | product_brand |
| 300 | BB |