Which SQL Keyword Is Used to Retrieve a Maximum Value

What is the SQL MAX Keyword?

The MAX() function is an aggregator function that will return the largest value in a number, date, or string column. You can use this function when you need to identify extreme values like the highest number, latest date, or largest string.

The basic syntax of the MAX() function is as follows.

SELECT MAX(column_name) FROM table_name;

This query will return the max value of a specific column from the table. The real use of MAX() is to combine it with other SQL clauses.

Let’s create Employees table and insert some sample data, and we will use this table for our future examples

-- Create Employees Table

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Name VARCHAR(100),
  Department VARCHAR(50),
  Salary DECIMAL(10, 2),
  Age INT
);

-- Insert Sample Data

INSERT INTO Employees (EmployeeID, Name, Department, Salary, Age)
VALUES
(1, 'John Doe', 'HR', 60000, 28),
(2, 'Jane Smith', 'IT', 95000, 35),
(3, 'Mark Johnson', 'IT', 105000, 40),
(4, 'Mary Brown', 'HR', 85000, 33),
(5, 'Lucy White', 'Marketing', 70000, 26),
(6, 'Tom Black', 'Marketing', 75000, 30),
(7, 'Alice Green', 'HR', 75000, 32),
(8, 'Bob Blue', 'IT', 98000, 38),
(9, 'Charlie Pink', 'Marketing', 60000, 27),
(10, 'Eve Red', 'IT', 110000, 42);

Related Topics:

What is SQL
How To Check Long Running Queries
Do SQL Views Update Automatically?
Check Table Size in SQL Server
Calculate Age from Date of Birth in SQL

Retrieving the Maximum Value from a Single Column

You can use MAX() function and pass the column name for which you want maximum value

Syntax:

SELECT MAX(Salary) AS Highest_Salary FROM Employees;

This query will return the highest salary from the Employees table. The output will look like below

Output:

How To Use MAX() Function with GROUP BY Clause

You can group the data by specific columns using GROUP BY clause. Suppose you want to fetch the highest salary of each department, you can use MAX() function with GROUP BY clause.

Syntax:

SELECT Department, MAX(Salary) AS Highest_Salary
FROM Employees
GROUP BY Department;

This query will group all the departments in table and show the highest salary in each group.

Output:

How To Use MAX() Function with HAVING Clause

You can use the HAVING Clause when you want to filter groups after aggregation, for example, if you want to see the department-wise salary where highest salary is greater than 80,000.

Syntax:

SELECT Department, MAX(Salary) AS Highest_Salary
FROM Employees
GROUP BY Department
HAVING MAX(Salary) > 80000;

This query will return all departments highest salaries, which exceeds 80,000.

Output:

How To Use MAX() Function with WHERE Clause

In most cases, the WHERE clause is used with MAX() functions. You can filter rows before applying MAX() functions using the WHERE clause. For example, if we want to find the maximum salary for employees older than 30.

Syntax:

SELECT MAX(Salary) AS Highest_Salary
FROM Employees
WHERE Age > 30;

Output:

How To Get Maximum Value with ORDER BY Clause

As we understand, we can get the maximum value of a column using MAX() function directly,  but here is another option to get the maximum value using ORDER BY to shorten the records.

Syntax:

SELECT TOP 1 Salary
FROM Employees
ORDER BY Salary DESC;

This query will sort the salaries in descending order and retrieve the top 1 result —the highest salary.

Output:

Advanced Use Cases

MAX() with Joins

The MAX() can be combined with joins to fetch additional details from different tables. For example, if you want the highest salary along with employee details, you can join the table with a department table.

Syntax:

SELECT e.Name, e.Department, MAX(e.Salary) AS Highest_Salary
FROM Employees e
JOIN Departments d ON e.Department = d.Department
GROUP BY e.Name, e.Department;

MAX() with Subqueries

You can use MAX() function in the subquery. For example, to find the employees who get the highest salary in the office.

Syntax:

SELECT Name, Salary
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);

SQL Query Performance

When working with large datasets, the performance of queries that use the MAX() function can be a concern. To improve performance:

• Ensure that the column used in the MAX() function is indexed.
• Avoid using MAX() with unfiltered data when possible, as this can be computationally expensive.

Conclusion

The MAX() function is a versatile and powerful tool in SQL for retrieving the highest value from a column, by combining it with other SQL clauses such as GROUP BY, HAVING, WHERE, and ORDER BY, you can tailor your queries to meet various needs. Whether you are working with large datasets or specific filters, the MAX() function is essential for analyzing extreme values efficiently. This blog has covered all the methods that answer the question, “Which SQL Keyword Is Used to Retrieve a Maximum Value” to help you effectively utilize this feature in your queries.