Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    India
    Posts
    6

    Unanswered: help with this query

    how do i find out the 3 highest salaries in the HR schema of the 9i db ... asking here for help on the structure of the query and how to go abt writing it ... my bulb is just fused out.

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Code:
    select column1, column2
    from your_table
    where rownum < 4
    order by salary_column
    Johan

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select *
    from
    (select *
    from table
    ORDER BY column)
    where rownum <=3;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Feb 2004
    Location
    India
    Posts
    5
    If you want to know top three salaries and suppose some of them are equal then the principle of rownum will not work.
    You may try this query.

    select EMPNO,SAL from emp a
    where 3 > (select count(*) from emp b
    where b.sal>a.sal);

    Please let me know if you have any concerns regarding this.

    Thanks and Regards,
    SANG GARG

  5. #5
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    .. or use the rank function

    select * from (
    select *, rank () over (order by sal_col) AS RANK from table_name
    ) where RANK <=4
    Oracle can do wonders !

  6. #6
    Join Date
    Feb 2004
    Location
    India
    Posts
    6

    Thumbs up

    Originally posted by SANG_GARG
    If you want to know top three salaries and suppose some of them are equal then the principle of rownum will not work.
    You may try this query.

    select EMPNO,SAL from emp a
    where 3 > (select count(*) from emp b
    where b.sal>a.sal);

    Please let me know if you have any concerns regarding this.

    Thanks and Regards,
    SANG GARG
    thanks man , works like a charm ....

  7. #7
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Thumbs down

    hi there

    dear mr. binman
    you said :

    thanks man , works like a charm ....

    for this select :

    select EMPNO,SAL from emp a
    where 3 > (select count(*) from emp b
    where b.sal>a.sal);

    suppose that u have the data below:
    sal 1000
    sal 1000
    sal 1000
    sal 1000
    sal 1000
    sal 900
    sal 900
    sal 900
    sal 900
    sal 800
    sal 800
    sal 800
    sal 200
    .
    .
    .
    the select above will return :
    1000
    1000
    1000
    1000
    1000

    i think this is not what r u looking for
    i think that u r looking for this

    select * from (select distinct(sal) from emp order by sal desc)
    where orwnum <= 3;

    try it and reply plz

  8. #8
    Join Date
    Feb 2004
    Location
    India
    Posts
    6

    Thumbs up

    thx for the update, i knda figured the distinct and desc part myself .... appreciate the effort tough ... the name is rehan . thx again.

  9. #9
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Originally posted by giggs11
    hi there

    dear mr. binman
    you said :

    thanks man , works like a charm ....

    for this select :

    select EMPNO,SAL from emp a
    where 3 > (select count(*) from emp b
    where b.sal>a.sal);

    suppose that u have the data below:
    sal 1000
    sal 1000
    sal 1000
    sal 1000
    sal 1000
    sal 900
    sal 900
    sal 900
    sal 900
    sal 800
    sal 800
    sal 800
    sal 200
    .
    .
    .
    the select above will return :
    1000
    1000
    1000
    1000
    1000

    i think this is not what r u looking for
    i think that u r looking for this

    select * from (select distinct(sal) from emp order by sal desc)
    where orwnum <= 3;

    try it and reply plz
    that's right becouse rownum dosn't work in subqueries it also doesn't
    work like this:
    select * from table where rownum is > 10
    request is no rows returned but you can use it like this:

    select * from
    (
    select col1, col2, ..., colN, rownum from table
    )
    where rownum > 10;

    it should works

Posting Permissions

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