sql query on highest salary [message #7401] |
Wed, 11 June 2003 08:20 |
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 |
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 |
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
|
|
|