Query to find second highest value per group

Question

Write a query to extract the department and second highest salary in each department

Employee

| Column         | Value          |
| -------------- | -------------- |
| employee_id    | int            |
| name           | string         |
| department     | string         |
| employment_type| string         |
| salary         | int            |

Table Image

What'S your question? Sounds like homework. What have you tried so far by yourself? — user743414, Jul 22 '21 at 12:10
Does this answer your question? [SQL select nth member of group](https://stackoverflow.com/questions/463054/sql-select-nth-member-of-group) — jdhao, Sep 20 '22 at 07:23

score 0 · Answer 1 · answered Jul 22 '21 at 12:15

0

I think this can help you.

SELECT department, MAX(salary) AS second_high FROM Employee
WHERE second_high < (SELECT MAX(salary) FROM Employee)
GROUP BY department

answered Jul 22 '21 at 12:15

Amir Hallaji

1
1

please add explanation, so the SP can understand – Ben.S Jul 22 '21 at 12:19

score 0 · Answer 2 · answered Jul 22 '21 at 14:12

Use window functions:

select e.department,
       max(case when seqnum = 2 then e.salary end) as second_highest
from (select e.*,
             dense_rank() over (partition by department order by salary desc) as seqnum
      from employee e
     ) e
group by e.department;

You can read the documentation on what dense_rank() does. In this case, it assigns "1" to the rows with the highest salary, "2" to the rows with the second highest, and so on.

Query to find second highest value per group

2 Answers2