Home » SQL & PL/SQL » SQL & PL/SQL » sql query on highest salary
sql query on highest salary [message #7401] Wed, 11 June 2003 08:20 Go to next message
Rosy
Messages: 3
Registered: December 2000
Junior Member
how can i get top 5 highest salary and bottom 3 lowest

salary from emp table in a single query.

thanks in advance...
Re: sql query on highest salary [message #7402 is a reply to message #7401] Wed, 11 June 2003 08:33 Go to previous messageGo to next message
SaschaV
Messages: 18
Registered: February 2003
Junior Member
Hi, this should give you the desired results. It takes the highest and lowers salaries and joins them in a union.
Hope this helps.

select * from (
    select *
    from (
        select *
        from salaries2
        order by salary DESC
    )
    where rownum <=5
    UNION
    select *
    from (
        select *
        from salaries2
        order by salary ASC
    )
    where rownum <=3
)
order by salary desc;
Re: sql query on highest salary [message #7403 is a reply to message #7401] Wed, 11 June 2003 10:09 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
If you are on at least version 8.1.6, you can use the DENSE_RANK() analytic function to solve this kind of problem:
SQL> SELECT   e.ename
  2  ,        e.sal
  3  FROM     emp   e
  4  ORDER BY e.sal
  5  /
  
ENAME             SAL
---------- ----------
SMITH             800
JAMES             950
ADAMS            1100
WARD             1250
MARTIN           1250
MILLER           1300
TURNER           1500
ALLEN            1600
CLARK            2450
BLAKE            2850
JONES            2975
SCOTT            3000
FORD             3000
KING             5000
  
14 rows selected.
  
SQL> SELECT   ename
  2  ,        sal
  3  ,        DECODE(SIGN(min_rank - 3)
  4           ,      +1, TO_NUMBER(NULL)
  5           ,      min_rank) rank_from_bottom   
  6  ,        DECODE(SIGN(max_rank - 5)
  7           ,      +1, TO_NUMBER(NULL)
  8           ,      max_rank) rank_from_top
  9  FROM    (SELECT e.ename
 10           ,      e.sal
 11           ,      DENSE_RANK() OVER (ORDER BY e.sal DESC)  max_rank
 12           ,      DENSE_RANK() OVER (ORDER BY e.sal ASC)   min_rank
 13           FROM   emp   e)
 14  WHERE    max_rank <= 5
 15  OR       min_rank <= 3
 16  ORDER BY rank_from_top
 17  ,        rank_from_bottom
 18  /
  
ENAME             SAL RANK_FROM_BOTTOM RANK_FROM_TOP
---------- ---------- ---------------- -------------
KING             5000                              1
SCOTT            3000                              2
FORD             3000                              2
JONES            2975                              3
BLAKE            2850                              4
CLARK            2450                              5
SMITH             800                1
JAMES             950                2
ADAMS            1100                3
  
9 rows selected.
  
SQL> 
Good luck,

A
Previous Topic: internal Cursor handling
Next Topic: ora1841 error message
Goto Forum:
  


Current Time: Thu Apr 25 17:34:28 CDT 2024