First Two Salaries In Each Department

I have a table called departments which consists of a column called salary. Now I want to have all the details of the highest two salaries in each department. How should we develop

Solution 1:

I realize you asked for this in Oracle. I can't help you there.

But, perhaps if you see the solution in MSSQL/TSQL it will help?

select 
     d.Salary
    ,d.Department
from
(
    select 
         r.Salary
        ,r.Department
        ,row_number() over(
            partition by r.Department
            orderby r.Salary desc) as RowNumber
    from HumanResources as r
) as d
where d.RowNumber < 3

Best of luck!

Solution 2:

DECLARE@TV_SAL TABLE (EMPID INT, DEPTID CHAR(10),SAL INT)

INSERTINTO@TV_SAL(EMPID,DEPTID,SAL) VALUES(4,'OR',1004)    
INSERTINTO@TV_SAL(EMPID,DEPTID,SAL) VALUES(5,'OR',1005)    
INSERTINTO@TV_SAL(EMPID,DEPTID,SAL) VALUES(1,'OR',1001)    
INSERTINTO@TV_SAL(EMPID,DEPTID,SAL) VALUES(2,'OR',1002)    
INSERTINTO@TV_SAL(EMPID,DEPTID,SAL) VALUES(17,'CS',1503)    
INSERTINTO@TV_SAL(EMPID,DEPTID,SAL) VALUES(18,'CS',1503)    
INSERTINTO@TV_SAL(EMPID,DEPTID,SAL) VALUES(14,'CS',1500)    
INSERTINTO@TV_SAL(EMPID,DEPTID,SAL) VALUES(15,'CS',1501)

SELECT*FROM@TV_SAL A     
 WHERE ( SELECTCOUNT(DISTINCT(SAL)) 
           FROM@TV_SAL B
          WHERE A.SAL <= B.SAL 
            AND A.DEPTID = B.DEPTID
                ) <=3-- Replace this with 1 ,2 or n , n indicates top n   ORDERBY DEPTID, SAL DESC

Solution 3:

SELECT TOP 5 b.DepartName,a.Salary FROM Employee a

JOIN

Department b ON a.DepartId=b.DepartId

group by b.DepartName ORDER BY a.Salary DESC

It will working fine on Sql server and i am not aware about Oracle