Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Abhishek.

Asked: August 04, 2016 - 3:45 pm UTC

Last updated: August 08, 2016 - 4:52 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

Its been such a long time to visit here and resolve the doubts but this is the first time I am asking.

There is an Employee table with Name, Department, Salary and Date of Joining in it. I need to write a query which will give the output of top 3 salaries in each department.

This means, in case I have 5 departments then I should have 5*3 =15 records in the result set. Each department can have multiple records.

I tried this using dense_rank and row_number functions but the requirement here is that the same rank should come in a single row meaning that if department "Accounts" has 2 people with the top salary of 10000, then both should come as rank 1 in same row and none of them should be eliminated.

Could you please help.

Best Regards,
Abhishek

and Connor said...

I'm not sure why dense_rank isnt doing what you need ?

Here's an example with

a) differing salaries
b) some same salaries
c) all same salaries

and it seems to give what you need 


SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( dept int, sal int );

Table created.

SQL>
SQL> insert into t values (1,1);

1 row created.

SQL> insert into t values (1,2);

1 row created.

SQL> insert into t values (1,3);

1 row created.

SQL> insert into t values (1,4);

1 row created.

SQL> insert into t values (1,5);

1 row created.

SQL>
SQL>
SQL> insert into t values (2,1);

1 row created.

SQL> insert into t values (2,1);

1 row created.

SQL> insert into t values (2,2);

1 row created.

SQL> insert into t values (2,2);

1 row created.

SQL> insert into t values (2,5);

1 row created.

SQL>
SQL> insert into t values (3,1);

1 row created.

SQL> insert into t values (3,1);

1 row created.

SQL> insert into t values (3,1);

1 row created.

SQL> insert into t values (3,1);

1 row created.

SQL> insert into t values (3,1);

1 row created.

SQL>
SQL> select *
  2  from (
  3  select t.*,
  4    dense_rank() over ( partition by dept order by sal ) as rk
  5  from t
  6  )
  7  where rk <= 3;

      DEPT        SAL         RK
---------- ---------- ----------
         1          1          1
         1          2          2
         1          3          3
         2          1          1
         2          1          1
         2          2          2
         2          2          2
         2          5          3
         3          1          1
         3          1          1
         3          1          1
         3          1          1
         3          1          1

13 rows selected.

SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Working same

Abhishek, August 08, 2016 - 11:43 am UTC

Thans for the response but it actually results in duplicate ranks for same salary. I tried below:

SELECT *
FROM (SELECT id,
salary,
dept,
DENSE_RANK () OVER (PARTITION BY dept ORDER BY salary DESC)
AS dr
FROM sal_test)
WHERE dr <= 5
ORDER BY dept, salary DESC


However, I want to have same salary and rank in single row and not in different rows.

How shall I go for that.
Chris Saxon
August 08, 2016 - 4:52 pm UTC

What is the output you expect? Give us some sample data along with what you should see when running the query!

Chris
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.