What are the top three salaries in each department using SQL?
When working with SQL, one common challenge is retrieving the top three highest salaries from each department within a company. This question often comes up in tech interviews and can be effectively tackled using a combination of window functions and common table expressions (CTEs). Below, we’ll explore how to achieve this in a straightforward manner.
Let’s assume we have an employees table structured like this:
Sql
To find the top three salaries in each department, we can utilize the ROW_NUMBER() window function. This function allows us to assign a unique sequential integer to rows within a partition of a result set. The basic SQL query to achieve this could look like the following:
Sql
Explanation of the SQL Query
-
Common Table Expression (CTE): The query begins with a CTE named
RankedSalaries. This allows us to create a temporary result set where we assign ranks to the salaries within each department. -
ROW_NUMBER() Function: Inside the CTE, we use the
ROW_NUMBER()function along withPARTITION BY department. This creates groups (or partitions) based on the department column and orders the salaries in descending order. Each salary within a department gets a rank starting from 1 for the highest salary. -
Filtering: After generating the ranked list of salaries, we retrieve only those entries where the rank (
salary_rank) is less than or equal to 3. This way, we filter our results to show only the top three salaries for each department. -
Final Selection: The final
SELECTstatement retrieves the relevant columns from our ranked salaries while ensuring the output is ordered first by department and then by salary in descending order.
Alternative Method Using DENSE_RANK()
If there is a chance that two employees in the same department could have the same salary, resulting in more than three employees being tied for the top three salaries, you might want to consider using the DENSE_RANK() function instead:
Sql
Using DENSE_RANK() ensures that if there are ties, the next distinct salary will still get a rank, allowing for a more inclusive set of results if needed.
By mastering this SQL technique, one can efficiently address the common inquiry of retrieving the top salaries within categorized departments, showcasing both analytical skills and SQL proficiency during tech interviews.
