CODEWITHSUNDEEP
sqloracle-databaseoracle10g
user1528050
user1528050

Reputation: 11

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

Upvotes: 1

Views: 63

Answers (2)

Joe G Joseph
Joe G Joseph

Reputation: 24086

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

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

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

Upvotes: 3

Related Questions