Second highest salary per department

Second highest salary per department

Medium SQL DQL 13 views
Explanation Complexity

Problem Statement

For each department, return employee(s) who have the second highest salary. Use DENSE_RANK so ties are handled nicely.

Input Format

SQL query

Output Format

Result set

Example

Employees(emp_id, dept_id, salary)
Rows per dept

Constraints

Think: window function PARTITION BY dept_id

Input / Output Format

Input Format
SQL query
Output Format
Result set
Constraints
Think: window function PARTITION BY dept_id

Examples

Input:
Employees(emp_id, dept_id, salary)
Output:
Rows per dept

Example Solution (Public)

SQL 
SELECT emp_id, dept_id, salary FROM (SELECT emp_id, dept_id, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk FROM Employees) t WHERE rnk = 2;

Official Solution Code

SELECT emp_id, dept_id, salary FROM (SELECT emp_id, dept_id, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk FROM Employees) t WHERE rnk = 2;
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.