CODEWITHSUNDEEP
oracle-databasesubquery
suvendu
suvendu

Reputation: 3

Oracle query to fetch top 3 salaries of each department

I want a SQL query to fetch top 3 salaries of each department

Table :- sample

Name   Salary   Dept
AA      1000    Hr
BB      7520    Store
CC     12500    Hr
DD      9850    Store
EE     10250    Finance
FF     12560    Hr
GG     13500    Store
HH     15680    Store
KK     12853    Hr
MM     17582    Finance
NN     16852    Finance

I used the below query but it is not fetching proper result

SELECT dept, fname,lname,sal from sample where rownum<4 group by(fname,lname,sal,desg) order by sal desc

Upvotes: 1

Views: 3227

Answers (4)

Sidharud Ekunde
Sidharud Ekunde

Reputation: 9

SELECT *
FROM sample a
WHERE
   3 >= ( SELECT COUNT(DISTINCT salary)
          FROM sample b
          WHERE a.salary <= b.salary
            AND a.dept = b.dept
        )

Upvotes: 0

Ramsai Dhanumuri
Ramsai Dhanumuri

Reputation: 1

SELECT * 
FROM(SELECT Name  ,Dept ,Salary  , 
DENSE_RANK() OVER (PARTITION BY Dept ORDER BY Salary  DESC)AS D_RANK 
FROM sample ) 
WHERE D_RANK <=3;

Upvotes: 0

Minh Kieu
Minh Kieu

Reputation: 475

Try group by and then where rownum < 4

SELECT dept,fname,lname,sal 
FROM sample 
GROUP BY(fname,lname,sal,desg)
WHERE ROWNUM < 4
ORDER BY sal DESC
;

Also check SELECT and GROUP BY columns as the table only showing NAME and not fname and lname.

Upvotes: 0

Pham X. Bach
Pham X. Bach

Reputation: 5442

What you need is the analytic function row_number

select * 
from (select a.*, row_number() over (PARTITION by dept order by salary desc) as num 
        from sample a
)
where num < 4;

Upvotes: 3

Related Questions