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
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