"Department's top 3 salaries" query in SQL

Creating a "department's top 3 salaries" Query in SQL is a powerful technique that enables HR departments and managers to explore employee salary data and extract valuable insights. Through the use of SQL's window functions and partitioning techniques, this query efficiently retrieves the top three salaries for each department, offering a comprehensive view of the most valued contributors in the organization.

Let's explore more about this with an example table Employees table.

Employees table

e_id

e_name

e_salary

e_dept_id

1

John Doe

50000

1

2

Jane Smith

55000

1

3

Boby Johnson

48000

1

4

Marry William

60000

2

5

Linda Davis

62000

2

6

James Lee

58000

2

Let's write the schema of the table and insert values in the table in SQL.

CREATE TABLE employees (
    e_id INT,
    e_name VARCHAR(100),
    e_salary INT,
    e_dept_id INT
);
INSERT INTO employees (e_id, e_name, e_salary, e_dept_id) VALUES (1, 'John Doe', 50000, 1);
INSERT INTO employees (e_id, e_name, e_salary, e_dept_id) VALUES (2, 'Jane Smith', 55000, 1);
INSERT INTO employees (e_id, e_name, e_salary, e_dept_id) VALUES (3, 'Boby Johnson', 48000, 1);
INSERT INTO employees (e_id, e_name, e_salary, e_dept_id) VALUES (4, 'Marry William', 60000, 2);
INSERT INTO employees (e_id, e_name, e_salary, e_dept_id) VALUES (5, 'Linda Davis', 62000, 2);
INSERT INTO employees (e_id, e_name, e_salary, e_dept_id) VALUES (6, 'James Lee', 58000, 2);
select * from employees

Before moving onto the actual query, let's understand two statements in SQL that will help us write the query.

  1. The ROW_NUMBER function

  2. The PARTITION BY clause

ROW_NUMBER()

  • The ROW_NUMBER() function is a window function in SQL that assigns a unique sequential number to each row in the result set, starting from 1 for the first row, 2 for the second row, and so on. It's used with the OVER() clause, which defines the window within which the function operates. The OVER() clause can include PARTITION BY, ORDER BY, and ROWS or RANGE clauses to specify the window frame.

The general syntax of the ROW_NUMBER() function is:

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ... 
)

PARTITION BY

  • The PARTITION BY clause is used in conjunction with window functions such as ROW_NUMBER() to divide the result set into partitions or groups based on the specified column(s).

  • When you use PARTITION BY in a window function, the function's calculations are performed separately for each partition, allowing you to get results on a per-group basis.

The general syntax of the PARTITION BY clause is:

SELECT column1, column2, ..., window_function() OVER (PARTITION BY partition_column1, partition_column2, ... ORDER BY sort_column)
FROM your_table;

Creating a department top 3 salaries query

Now that we have understood the utility functions for our SQL query let's use them.

SELECT * FROM (
    SELECT e.*, ROW_NUMBER() OVER(
        PARTITION BY e_dept_id 
        ORDER BY e_salary DESC) AS rn
    FROM employees e
) AS t
WHERE t.rn <= 3;

Code explanation

The subquery uses the ROW_NUMBER() function along with PARTITION BY to rank employees within each department based on their salaries. The outer query then filters the results to only include the top 3 salaries for each department, as determined by the row numbers assigned within each department.

  • Line 1–4: The sub-query uses a window function ROW_NUMBER(), along with PARTITION BY to generate row numbers for each row within specific partitions defined by the department ID (e_dept_id). The inner subquery retrieves all columns (e.*) from the "employees" table and adds an additional column named, which represents the row number calculated for each row within its department.

  • Line 4–5: The ROW_NUMBER() function is applied within each department separately, and the rows are ordered based on the salary in descending order (ORDER BY e_salary DESC). This means that employees with higher salaries get lower row numbers within each department.

  • Line 6: The outer query aliases the subquery as t.

  • Line 7: The WHERE t.rn <= 3 condition is applied to the outer query. It filters the results to include only those rows where the row number (rn) is less than or equal to 3, effectively selecting the top 3 salaries for each department.

Here's the output table with each department's employees who have the top 3 salaries in their department.

Output table

e_id

e_name

e_salary

e_dept_id

rn

2

Jane Smith

55000

1

1

1

John Doe

50000

1

2

3

Boby Johnson

48000

1

3

5

Linda Davis

62000

2

1

4

Marrt William

60000

2

2

6

James Lee

58000

2

3

Free Resources

Copyright ©2026 Educative, Inc. All rights reserved