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.
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.
The ROW_NUMBER function
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 BYThe 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;
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_idORDER BY e_salary DESC) AS rnFROM employees e) AS tWHERE t.rn <= 3;
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.
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