two highest and three lowest salaries (was "Query")

sashish4529 · #1 (**permalink**) 02-28-05, 02:28

Hi,

I need to construct a query for table "emp" where in it is possible to pick the two people drawing the highest salary in desc order and botom 3 people drawing lowest salary in ascending order in one result set.

The query
select *
from
( select empno,ename,sal
from emp
order by sal asc )
where rownum < 3

will give me top 2 people

and the query
select *
from
(select empno,ename,sal
from emp
order by sal desc )
where rownum < 4

will give me bottom 3 people.

However when I use
select *
from
( select empno,ename,sal
from emp
order by sal asc )
where rownum < 3
union
select *
from
(select empno,ename,sal
from emp
order by sal desc )
where rownum < 4

the results are jumbled.

Any way out ?

Many Thanks.
Ash

Littlefoot · #2 (**permalink**) 02-28-05, 08:22

This is just an idea ... see if it (or its variations) might help you:

Code:

SELECT   empno, ename, sal
    FROM (SELECT   empno, ename, sal
              FROM EMP
          ORDER BY sal ASC)
   WHERE ROWNUM < 3
UNION
SELECT   empno, ename, sal
    FROM (SELECT   empno, ename, sal
              FROM EMP
          ORDER BY sal DESC)
   WHERE ROWNUM < 4
ORDER BY sal;

r937 · #3 (**permalink**) 02-28-05, 08:30

ash, this is the sql forum

if you want oracle, please post in the oracle forum

let's have standard sql solutions, please

Code:

select X.eno
     , X.ename
     , X.sal
  from emp X
left outer
  join emp Y
    on Y.sal > X.sal
group 
    by X.eno
     , X.ename
     , X.sal
having count(*) < 2
union all
select X.eno
     , X.ename
     , X.sal
  from emp X
left outer
  join emp Y
    on Y.sal < X.sal
group 
    by X.eno
     , X.ename
     , X.sal
having count(*) < 3

Internet Services

Web Development

Digital Marketing

Consumer Tech