CODEWITHSUNDEEP
sqloracle-database
Zeb
Zeb

Reputation: 129

About sql subquery

What to do when we want to select salary of a employee greater than many (lets say 12) employees's salary from a table. I know that we'll have to use a subquery but writing it as :-

Select ename,salary 
from emp 
where salary>( select salary
            from emp
           where ename='A'||ename='B'.....)

it could be written like that but its not a good approach. Please suggest some useful query for it.

Upvotes: 0

Views: 77

Answers (4)

Ronnis
Ronnis

Reputation: 12833

I can see two different interpretations of your requirement.

1. What employees earn more than 12 other (random) employees

and

2. What employees earn more than 12 specific employees

This query solves the first requirement, although it will become slow as hell on larger datasets.

select *
  from emp a
 where 12 = (select count(*) 
               from emp b 
              where b.salary < a.salary);

This query solves the second requirement

select * 
  from emp
 where salary > all(select salary 
                      from emp 
                     where emp_id in(1,2,3,4,5)
                   )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you know the 12 employees, I think you want to write the query as:

Select ename,salary 
from emp 
where salary> (select max(salary)
               from emp
               where ename in ('A', 'B', . . . )
              )

IN is much more convenient than a bunch of or statements. And, the subquery needs to return one value, the maximum salary.

Upvotes: 1

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

Select ename,salary 
from emp 
where salary > (
    select salary 
    from 
        (
        select 
           salary, 
           rownum as rn
        from emp
        order by salary
        )
    where rn = 12
)

Upvotes: 1

Kamil
Kamil

Reputation: 13931

This is not exact code that you may use, but it should help you.

You can use RANK() function.

Example from article at oracle-base.com:

SELECT empno,
       deptno,
       sal,
       RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM   emp;

     EMPNO     DEPTNO        SAL       rank
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          4
      7499         30       1600          5
      7698         30       2850          6

Upvotes: 0

Related Questions