Sql subquery question

1

Hello guys ,

These are given column names
" EMPNO" , "ENAME ", " JOB", " MGR"," HIREDATE" ,"SALARY", "COMMISSION", "DEPTNO"
name under "EMP' table , i have to find first three maximum salary ?? Please assume oracle db .

SQL> select * from emp;

 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

  7369 SMITH      CLERK           7902 17-DEC-80        800                    20
  7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
  7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
  7566 JONES      MANAGER         7839 02-APR-81       2975                    20
  7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
  7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
  7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
  7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
  7839 KING       PRESIDENT            17-NOV-81       5000                    10
  7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
  7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
  7900 JAMES      CLERK           7698 03-DEC-81        950                    30
  7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
  7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

Regards;

TS

2

select top 3 sal from emp
order by sal desc

1 Like
3

There's no 'top' keyword in oracle . Please do it with co related sub query if possible ?

4

This is a Microsoft SQL Forum, folk here are unlikely to be familiar with Oracle syntax.

1 Like
5

Try this:

select empno
      ,ename
      ,job
      ,mgr
      ,hiredate
      ,salary
      ,commission
      ,deptno
  from emp
 order by salary desc
 fetch next 3 rows only
;

or this:

select empno
      ,ename
      ,job
      ,mgr
      ,hiredate
      ,salary
      ,commission
      ,deptno
  from (select empno
              ,ename
              ,job
              ,mgr
              ,hiredate
              ,salary
              ,commission
              ,deptno
          from emp
         order by salary desc
       )
 where rownum<=3
;
1 Like
6

select empno from emp 1
where 3<= (select count(distinct emp2.sal) from emp 2
where emp2.sal >=emp1.sal)

1 Like
8

Thank you, guys :slight_smile: i have found the answer .
select * from emp x
where 3>= (select count(distinct sal) from emp
where x.sal <=sal);

9

What you are doing here is actually called "dense rank" and there's a built-in function for that, that's much more flexible than, what you are asking for - but definitely worth looking into.

For your case:

select *
  from (select *
              ,dense_rank() over(order by sal desc) as r
          from emp
       ) as e
 where e.r<=3
;

Lets say you wanted top 3 saleries (dense rank) foreach job, you would simply add partition statement into the over() function like this:

select *
  from (select *
              ,dense_rank() over(partition by job
                                 order by sal desc
                                )
               as r
          from emp
       ) as e
 where e.r<=3
;

And best of all - it also works for sql server, so you code is portable