Query the names of employees with the second and third highest salary in each department in Oracle

CSDN Q & A 2022-02-13 06:10:42 阅读数:910

query names employees second highest

Oracle Query the names of the second and third highest paid employees in each department
It can only be written in one sentence, not in two steps




Take the answer :
select * from (select department , full name ,row_number() over(partition by department order by salary desc) as rid from surface ) a where rid in (2,3) order by department ,rid

oracle Are there in row_number Open the window function , Use row_number Group parts , And rank by salary to get the ranking serial number , If there is a duplicate salary , use rank or dense_rank Instead of row_number, Specifically oracle I'm not sure if these two functions in this are the same name , You can try



Other answers 2:

select * from (
select *,row_number() over(partition by staff , department order by salary desc) as rn
) a where rn = 2 or rn = 3


Other answers 3:

First, sort the original table in descending salary order , Then take it as the original table and find out the top three , Group by Department , And in ascending salary order , Then take it as the original table , Select the first two , Group by Department , Reverse the order again

copyright:author[CSDN Q & A],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130610400857.html