How to Select Rows with Max Value on a Column in SQL

In SQL, the maximum value on a column can be selected by many methods, such as using GROUP BY with MAX(), using subquery, using JOIN, etc. In this blog, let us explore all the methods in detail.

Table of Contents: 

• Methods to SELECT Rows with MAX Value on a Column in SQL
  • Method 1: Using GROUP BY with HAVING in SQL
  • Method 2: Using SUBQUERY in SQL
  • Method 3: Using JOIN in SQL
  • Method 4: Using RANK() or ROW_NUMBER in SQL
  • Method 5: Using DISTINCT with a Subquery in SQL
  • Method 6: Using LIMIT with Order By in SQL
  • Method 7: Using MAX() in the SELECT Clause and GROUP BY in SQL
  • Method 8: Using FIRST_VALUE() in SQL
  • Method 9: Using CROSS JOIN with a Subquery in SQL
• Real-World Examples
• Performance Comparison for each method
• Conclusion

Methods to SELECT Rows with MAX Value on a Column in SQL

Before going into the methods, let us first create an employee table that can be used as an example for the following methods.

– Create an Employee table
CREATE TABLE Employee (
    Employee_id INT PRIMARY KEY,
    Employee_dept NVARCHAR(50),
    Salary DECIMAL(10,2)
);

-- Insert sample data into the Employee table
INSERT INTO Employee (Employee_id, Employee_dept, Salary)
VALUES
(101, 'TCW', 55000.00),
(102, 'TCW', 75000.00),
(103, 'TRA', 80000.00),
(104, 'HR', 60000.00),
(105, 'BDA', 95000.00),
(106, 'HR', 90000.00),
(107, 'TRA', 60000.00),
(108, 'HR', 70000.00),
(109, 'BDT', 95000.00),
(110, 'IT', 85000.00);

-- To display the Employee table
Select * from Employee;

This is how the table looks like once it is created and inserted with these values

Method 1: Using GROUP BY with HAVING in SQL

This method is beneficial when we are working with aggregated data.

Syntax: 

SELECT column1, column2, MAX(column3) AS max_value
FROM table_name
GROUP BY column1, column2
HAVING MAX(column3) = (SELECT MAX(column3) FROM table_name);

Example: Let us find the row with the maximum salary per department in the employee table using the GROUP BY with HAVING

SELECT Employee_id, Salary
FROM Employee
GROUP BY Employee_id, Salary
HAVING Salary = (SELECT MAX(Salary) FROM Employee);

Output: 

Explanation: The having clause filters the results to only return the groups where Salary is the maximum 

Method 2: Using SUBQUERY in SQL

This method retrieves the rows where the values in the specified column match the maximum value found in that column.

Syntax: 

SELECT column1, column2, column3
FROM table_name
WHERE column3 = (SELECT MAX(column3) FROM table_name);

Example: 

SELECT Employee_id, Employee_dept, Salary
FROM Employee
WHERE Salary = (SELECT MAX(Salary) FROM Employee);

Output: 

Explanation: The subquery returns the maximum salary of the employee table, and the WHERE clause selects the employees whose salary matches the highest salary found in the subquery.

Method 3: Using JOIN in SQL

We can also use a JOIN to achieve the same result, which may be useful when you want to join with another table.

Syntax: 

SELECT t1.*
FROM table_name t1
JOIN (SELECT MAX(column3) AS max_value FROM table_name) t2
ON t1.column3 = t2.max_value;

Example:

To find the employee with the highest salary

SELECT E.*
FROM Employee E
JOIN (SELECT MAX(Salary) AS MaxSalary FROM Employee) AS MaxTable
ON E.Salary = MaxTable.MaxSalary;

 Output: 

Explanation: The subquery finds the highest salary of the employee table and stores the result as a temporary table with a single column as MaxSalary. “ ON E.Salary = MaxTable.MaxSalary “This matches employees whose salary is equal to the maximum salary found in the subquery.

Method 4: Using RANK() or ROW_NUMBER in SQL

If we want to handle ties and retrieve multiple rows with the same maximum value, you can use a window function like RANK() or ROW_NUMBER() in SQL.

Syntax:

WITH RankedRows AS (
    SELECT *, 
           RANK() OVER (ORDER BY column3 DESC) AS Rank
    FROM table_name
)
SELECT * 
FROM RankedRows 
WHERE Rank = 1;

Example:

WITH RankedEmployee AS (
    SELECT Employee_id, Employee_dept, Salary,
           RANK() OVER (ORDER BY Salary DESC) AS Rank
    FROM Employee
)
SELECT Employee_id, Employee_dept, Salary
FROM RankedEmployee
WHERE Rank = 1;

Output:

Explanation: Uses the RANK() window function to rank employees based on their salary in descending order. Employees with the highest salary get RANK = 1. The SELECT statement retrieves employees where RANK=1, meaning those with the highest salary.

Method 5: Using DISTINCT with a Subquery in SQL

This method ensures that the subquery returns unique values, which can help in filtering out duplicates before processing the main query.

Syntax:

SELECT DISTINCT column1, column2, column3
FROM table_name
WHERE column3 = (SELECT MAX(column3) FROM table_name);

Example: 

SELECT DISTINCT Employee_id, Employee_dept, Salary
FROM Employee
WHERE Employee_id= (SELECT MAX(Employee_id) FROM Employee);

Output:

Explanation: The subquery selects the record of the employee whose employee_id is maximum. This DISTINCT keyword displays only the unique records from the subquery.

Method 6: Using LIMIT with Order By in SQL

If you’re using an older version of MySQL that does not support window functions, you can achieve the same result using ORDER BY and LIMIT in SQL

Syntax:

SELECT column1, column2, column3
FROM table_name
ORDER BY column3 DESC
LIMIT 1;

Example: To find employees with the  highest salary

SELECT Employee_id, Employee_dept, Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1;

Output:

Explanation: In SQL, the LIMIT with ORDER BY can be used for fetching the employees with the highest salary.

Method 7: Using MAX() in the SELECT Clause and GROUP BY in SQL

This allows us to retrieve the highest value in a specific column for each group of records.

Syntax:

SELECT column1, MAX(column2) AS max_value
FROM table_name
GROUP BY column1;

Example: 

SELECT Employee_dept, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY Employee_dept;

Output:

Explanation: The MAX() function returns the maximum value in the group, while GROUP BY groups the records based on one or more columns.

Method 8: Using FIRST_VALUE() in SQL

The FIRST_VALUE() function is a window function that returns the first value of a specified column in an ordered partition of a result set.

Syntax:

SELECT column1, column2, column3, 
       FIRST_VALUE(column3) OVER (ORDER BY column3 DESC) AS MaxValue
FROM table_name;

Example:

WITH RankedEmployee AS (
    SELECT Employee_id, Salary, FIRST_VALUE(Salary) OVER (ORDER BY Salary DESC) AS MaxSalary
    FROM Employee
)
SELECT Employee_id, Salary
FROM RankedEmployee
WHERE Salary = MaxSalary;

Output:

Explanation: Create a Common Table Expression named RankedEmployee, which acts as a temporary result set. Employees with the highest salary get Rank = 1.

Method 9: Using CROSS JOIN with a Subquery in SQL

The CROSS JOIN with subquery is used to generate a Cartesian product between a table and a result of the subquery.

Syntax:

SELECT t1.*
FROM table_name t1
CROSS JOIN (SELECT MAX(column3) AS max_value FROM table_name) t2
WHERE t1.column3 = t2.max_value;

Example:

SELECT E.*
FROM Employee E
CROSS JOIN (SELECT MAX(Salary) AS MaxSalary FROM Employee) AS MaxSalaryTable
WHERE E.Salary = MaxSalaryTable.MaxSalary;

Output:

Explanation: The CROSS JOIN combines every row from Employee with the single row result from MaxSalaryTable. Only Employees whose salary matches the maximum salary are selected.

Real-world Examples

1. Finding the top-selling companies: A retail company wants to find the top 2 best-selling products in each category.

Example:

--Create the table
CREATE TABLE Products (
    Product_ID INT PRIMARY KEY,
    Product_Name VARCHAR(100),
    Category VARCHAR(50),
    Quantity_Sold INT
);

--Insert sample data
INSERT INTO Products (Product_ID, Product_Name, Category, Quantity_Sold) VALUES
(1, 'iPhone 14', 'Electronics', 500),
(2, 'Samsung Galaxy S23', 'Electronics', 450),
(3, 'MacBook Pro', 'Electronics', 300),
(4, 'Nike Air Max', 'Footwear', 600),
(5, 'Adidas Ultraboost', 'Footwear', 550),
(6, 'Puma Sneakers', 'Footwear', 400);

--Query to find the top 2 best-selling products per category
SELECT * FROM (
    SELECT Product_Name, Category, Quantity_Sold, 
           RANK() OVER (PARTITION BY Category ORDER BY Quantity_Sold DESC) AS Product_Rank
    FROM Products

) ranked
WHERE Product_Rank <= 2;

Output:

Explanation: The RANK() command assigns a ranking to each product within its category and the PARTITION BY Category ensures that the ranking is reset for each category. The ORDER BY ranks the products from highest to lowest sales. 

2. HR System: An HR system needs to list employees earning above their average salary 

Example:

--Create a table 
CREATE TABLE Employees (
    Employee_ID INT PRIMARY KEY,
    Employee_Name VARCHAR(100),
    Department VARCHAR(50),
    Salary DECIMAL(10,2)
);

--Insert sample data 

INSERT INTO Employees (Employee_ID, Employee_Name, Department, Salary) VALUES(1, 'Alice', 'IT', 80000),
(2, 'Bob', 'IT', 75000),
(3, 'Charlie', 'Finance', 70000),
(4, 'David', 'Finance', 90000),
(5, 'Emma', 'IT', 95000);

--To find the employees earning more than their department’s average 
SELECT Employee_Name, Department, Salary 
FROM Employees 
WHERE Salary > (SELECT AVG(Salary) FROM Employees e2 WHERE e2.Department = Employees.Department);

Output:

Explanation: The inner query ensures that the average salary is computed only for the department of the current employee. The WHERE Clause filters employees whose salary is greater than the average salary of their department.

Performance Comparison for Each Method

Conclusion

You can get the last record in each group in MySQL using JOIN with MAX(), ROW_NUMBER(), MAX(ID), or CTE. These methods sort data based on given conditions. Indexing makes them faster, and ROW_NUMBER() is efficient. But when the data updates often, it may slow down. In such cases, CTE with ROW_NUMBER() works better. Choose the method based on your data size. Understanding these methods helps you to effectively fetch the last record in each group using MySQL.

FAQs