How to combine the MAX() function with the WHERE clause in SQL

Overview

The MAX() function is used with the WHERE clause to gain further insights from our data. In SQL, the MAX() function computes the highest or maximum value of numeric values in a column.

Syntax

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Example

Let’s assume we have a Person table with columns such as name, salary, state, and gender.

We want to get the maximum salary paid in Lagos.

The following code shows how to use the MAX() function with the WHERE clause in SQL:

CREATE TABLE Person (
    ID int,
    name varchar(100),
    salary int,
    gender varchar(10),
    state varchar(15)
);
-- Insert data
INSERT INTO Person
VALUES (1,'Sharon Peller',40000,'Female','Kogi');
INSERT INTO Person
VALUES (2,'Paul Dons',150000,'Male','Lagos');
INSERT INTO Person
VALUES (3,'Ameera Abedayo',200000,'Female','Imo');
INSERT INTO Person
VALUES (4,'Maria Asaad',320000,'Female','Lagos');
INSERT INTO Person
VALUES (5,'David Hassan',250000,'Male','Abuja');
INSERT INTO Person
VALUES (6,'Niniola Disu',80000,'Female','Lagos');
INSERT INTO Person
VALUES (7,'Praise Dominion',340000,'Female','Lagos');
INSERT INTO Person
VALUES (7,'Divine Favour',280000,'Female','Abuja');
INSERT INTO Person
VALUES (7,'Praise Dominion',100000,'Female','Lagos');
INSERT INTO Person
VALUES (8,'Joe Smith',75000,'Lagos');
-- Query 
SELECT MAX(salary)
FROM Person
WHERE state = 'Lagos';

Explanation

  • Lines 1–7: We create a Person table with the columns: id, name, salary, gender, and state.
  • Lines 10–29: We insert data into the Person table.
  • Lines 31–35: We got the maximum salary paid in Lagos using the MAX() function with the WHERE() clause.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)