To select n th highest
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: To select n th highest

  1. #1
    Join Date
    Feb 2002
    Posts
    267
    hi folks,

    Could any body post an sql statement to select the N th highest value in a column.

    I found one....but had hard time understaning how it works


    >select L, max(sal)
    from emp where LEVEL=2
    where connect prior by sal>sal
    group by LEVEL;


    Could any body explain what this "connect prior by " is used for.

    Pls post if there is any better query

    Regards
    sonia

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Code:
    select min(sal) from
    (select sal from
      (select sal from emp order by sal desc)
      where rownum <=2
    )
    CONNECT BY clause and LEVEL pseudocolumn are useful for queries against tables containing hierarchical data. An example is the EMP table, an employee has a manager which also can have a manager and so on.

    Although the query you posted is usable, using CONNECT BY and LEVEL in this context is rather matter of interest.

    I'd preferably use the query posted by me at least for the performance reason.

    Ales

  3. #3
    Join Date
    May 2002
    Posts
    108

    Hope this helps !

    Sonia !

    1) The query you have posted doesn't seem to work alteast on my system! Can u check and post the appropriate query using CONNECT clause !

    2) As u require the nth maximum.. the query below will help you. Pass the required value for 'n'

    Ales !

    If I am right, your query will help in finding the 'n'th value, but not the 'n'th maximum value... correct me if I am wrong!

    Pls. check out if the following is a soln.,

    select * from (select sal, rownum rno from (select distinct sal
    from t_emp order by sal desc))
    where rno = &n

    Cheers,
    Nandu

    Never give up !

    Nanda Kumar - Vellore

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Some adjustment to make query work:
    Code:
    select LEVEL, max(sal)
    from emp where LEVEL=2
    connect by prior sal>sal
    group by LEVEL
    (takes 3 sec against 14 row EMP table, gives correct answer)

    Yes, you're right, I should add DISTINCT in the inner query... :-(

    Ales



  5. #5
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    If you looking for preformance use this:

    SELECT SAL, TOP_SAL
    FROM
    (SELECT SAL, ROW_NUMBER() OVER(ORDER BY SAL DESC NULLS LAST) TOP_SAL
    FROM KEY_KEYWORD)
    WHERE TOP_SAL <= 5; -- TOP 5 SAL.

  6. #6
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    Or to 5 distinct sal

    SELECT DISTINCT SAL, TOP_SAL
    FROM
    (SELECT KEYWORD, DENSE_RANK() OVER(ORDER BY KEYWORD_ID DESC NULLS LAST) TOP_SAL
    FROM EMP)
    WHERE TOP_SAL <= 5; -- TOP 5 SAL.

Posting Permissions

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



Click Here to Expand Forum to Full Width