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:
- Under a single field
- After joining tables
- 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:
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:
(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:
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:
Note that the WHERE clause needs to be placed before the GROUP BY.