Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    29

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

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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;
    Last edited by Littlefoot; 02-28-05 at 09:25.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •