First two salaries in each department

Question

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 a query which yields the required output? Top-N analysis will give it as a whole but not for each department. I want the top two in each department.

score 5 · Answer 1 · answered Jul 06 '11 at 17:30

5

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
            order by r.Salary desc) as RowNumber
    from HumanResources as r
) as d
where d.RowNumber < 3

Best of luck!

answered Jul 06 '11 at 17:30

Jerry Nixon

31,313
14
117
233

1

+1: Oracle has supported analytic functions since 9i (actually 8i, but I can't remember the specifics), along with the `WITH` clause (Oracle calls it "Subquery factoring"). – OMG Ponies Jul 06 '11 at 17:42
3

This should work in Oracle as written. However, using `rank` over `rownumber` may be preferable. `rownumber` will ensure that you always get 2 rows back, but, if the top salary is the same between 5 people, you'll get two of those rows, semi-randomly, with no guarantee of consistency. In the same scenario `rank` will return all 5 consistently. – Allan Jul 06 '11 at 17:47
Allan,Can you please share the above discussed query with "Rank" as you have shared in the comments – mayank agrawal Apr 10 '17 at 06:09

Shamil Kattungal · Answer 2 · 2012-10-16T14:55:49.317

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

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

SELECT * 
  FROM @TV_SAL A     
 WHERE ( SELECT COUNT(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   
 ORDER BY DEPTID, SAL DESC

Care to add some comments to your code? How you get the top of each department? — Yaroslav, Oct 05 '12 at 06:12

score -2 · Answer 3 · answered Apr 10 '17 at 06:06

-2

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

answered Apr 10 '17 at 06:06

mayank agrawal

628
5
20

First two salaries in each department

3 Answers3

Related