Skip to Main Content
  • Questions
  • 2 highest salary department wise ..alternate method



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.

Question and Answer

Connor McDonald

Thanks for the question, mayank.

Asked: June 26, 2016 - 7:36 pm UTC

Answered by: Connor McDonald - Last updated: June 27, 2016 - 12:48 am UTC

Category: Database - Version: 11g xe

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Fine-tuning the Quiz-taking Experience on the Oracle Dev Gym

You Asked

select * from employees e1
where :parameter=(select count(distinct e2.salary)
from employees e2
where e1.salary<=e2.salary);

--hi can anyone explain me this query .i know this can be solve using rank,dense_rank but confused while using this method

and we said...

Let's do it as a scalar query to see how it pans out

SQL> select empno,
  2         sal,
  3         ( select count(distinct sal)
  4           from   emp e2
  5           where  e1.sal<=e2.sal)
  6  from emp e1;

---------- ---------- ------------------------------------------------------
      7369        800                                                     12
      7499       1600                                                      6
      7521       1250                                                      9
      7566       2975                                                      3
      7654       1250                                                      9
      7698       2850                                                      4
      7782       2450                                                      5
      7788       3000                                                      2
      7839       5000                                                      1
      7844       1500                                                      7
      7876       1100                                                     10
      7900        950                                                     11
      7902       3000                                                      2
      7934       1300                                                      8

So you can see for each row, we are counting all the distinct salaries in the table that are greater than or equal to the salary we are currently on.

Thus when that count is (say) 2, we know that the row were are on, has a sal that is either matched or exceeded by at most one other salary in the table.

Hope this helps.

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