Oracle 10 g -SQL

How to find top 2 salaries in each department in emp table?

Emp Table
-----------
Row_id Salary Dept 
R1       2000    D1
R2       3000    D1
R3       4000    D1
R4       5000    D1
R5       2000    D2
R6       3000    D2
R7       4000    D2
R8       5000    D2

Answers


select 
    row_id,salary,dept 
from
(
select 
    row_number() over (partition by dept order by salary desc) as sno,
    row_id,salary,dept 
from emp
) t 
where sno<=2

please try this

select T1.Dept,T2.Salary  
from Emp_Table T1  join Emp_Table T2
on T1.Dept=T2.Dept
and T1.Salary>=T2.Salary
group by T1.Dept,T2.Salary 
having COUNT(*) <=2

Need Your Help