Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2003
    Location
    MA,USA
    Posts
    18

    Unanswered: nth highest val.

    nth highest value in a column
    Last edited by karjee96; 11-02-04 at 12:08. Reason: reply found in this forum

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    This should work (i don't exactly know the schema) :

    Code:
    SELECT MIN(Salary) FROM
    (SELECT Salary 
    FROM Salgrade 
    ORDER BY Salary DESC)
    WHERE rownum <= n;
    Regards,

    RBARAER

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Grrrrrrrr !

    If you edit your question before i post the answer, the world is upside down !

    Hope what I told you might help anyway. Could you please show me the other answer you found ?

    Regards,

    RBARAER

  4. #4
    Join Date
    Nov 2003
    Location
    MA,USA
    Posts
    18
    thanks for your help. i found this relatively simpler than most of the replies posted earlier in this forum

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    select column_name from
    (select column_name,
      
    count(column_name
          
    over (partition by column_name order by column_name ascnth
    from  table_name
    )
    where nth = &nth_highest_value
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello The_Duck,

    Sorry but I tried your query, and it took a very long time compared to mine (I tried on a non-indexed column with some millions of rows, and yours took 18 minutes vs 25 seconds for mine), and moreover did not give the correct result at all. (it returned 3000 results, among which there were 3 distinct values, none being the right one).

    Regards,

    RBARAER

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    you can do it this way using analytics.

    select column_name from
    (select column_name,
    dense_rank() over (order by column_name asc) nth
    from table_name)
    where nth = &nth_highest_value;

    You could replace dense_rank() with rank() if you like your ranking that way.

    As with all things Oracle there is no single right way of doing it. Instead there are lots of ways which you should try in your production environment to see which is the best.

    Alan

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello AlanP,

    Ok for your query, provided you replace "asc" by "desc" in the order by, because you are getting the nth lowest value, not greatest. With dense_rank(), it works just the same as the query I proposed, provided you add a DISTINCT in my inner query (which should have already been there ) :

    [CODE]SELECT MIN(column_name) FROM
    (SELECT column_name
    FROM table_name
    ORDER BY column_name DESC)
    WHERE rownum <= n;[CODE]

    You're right when saying that there are plenty of ways to do the same thing in Oracle, and it's always good to see that it's true. You're also right to say that it is always useful to try several solutions because behaviours of some queries may change depending on many criterias (test/prod environment for example).

    However, you seemed to defend The_Duck here, while I was not saying that there were no other solution, just that the one The_Duck proposed did not give the correct result. There were no offence at all because i have seen many insightful answers by The_Duck in this forum.

    I hope there is no more misunderstanding, if there were any.

    Still, AlanP, in my environment, the query I proposed is much faster than yours : 25 secs vs 4mins 20 secs.

    Regards,

    RBARAER

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    RBARAER,

    Your query may be quicker, but it isn't necessarily correct. It assumes that the column in question is unique:
    Code:
    SQL> create table table_name as
      2  select floor(rownum/10) column_name
      3  from all_objects
      4  where rownum < 1000;
    
    Table created.
    
    SQL> select column_name
      2  from table_name
      3  order by column_name desc;
    
    COLUMN_NAME
    -----------
             99
             99
             99
             99
             99
             99
             99
             99
             99
             99
             98
             98
             98
    ...
    SQL> SELECT MIN(column_name) FROM
      2  (SELECT column_name
      3  FROM table_name
      4  ORDER BY column_name DESC)
      5  WHERE rownum <= 9;
    
    MIN(COLUMN_NAME)
    ----------------
                  99
    The correct result is:
    Code:
    SQL> SELECT MIN(column_name) FROM
      2  (SELECT DISTINCT column_name
      3  FROM table_name
      4  ORDER BY column_name DESC)
      5  WHERE rownum <= 9;
    
    MIN(COLUMN_NAME)
    ----------------
                  91
    How does that DISTINCT affect your relative timings?

  10. #10
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello Tony,

    Well, if you read what I wrote (and haven't edited), I said I had to add the DISTINCT to the query in my first post... but I have omitted to do it in my "new" query.

    So, you're right, the distinct is necessary, and does not affect my timings (well, 27 secs instead of 25 secs, I'm cheating a little )

    Anyway, thanks for correcting my query, but it was only a typing error (I also omitted the "/" in [/CODE]).

    Regards,

    RBARAER

  11. #11
    Join Date
    Nov 2003
    Location
    MA,USA
    Posts
    18
    Hi AlanP,
    When talking about RANK() and DENSE_RANK();
    I guess DENSE_RANK() gives the correct result over RANK() .
    Using scott/tiger schema:

    SELECT DISTINCT Sal
    FROM
    (SELECT Sal,
    DENSE_RANK() OVER
    (ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
    FROM Emp
    ORDER BY SAL Desc NULLS LAST)
    WHERE Emp_Rank = 5;

    SELECT DISTINCT Sal
    FROM
    (SELECT Sal,
    RANK() OVER
    (ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
    FROM Emp
    ORDER BY SAL Desc NULLS LAST)
    WHERE Emp_Rank = 5;

    Any suggestions?
    Thanks
    lucy

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by RBARAER
    Hello Tony,

    Well, if you read what I wrote (and haven't edited), I said I had to add the DISTINCT to the query in my first post... but I have omitted to do it in my "new" query.
    I missed that, sorry.

    As for the significant timing difference you get, it surprises me and does not tally with my experience. On 8.1.7. I'm getting 135 secs for your query versus 167 secs for Alan's, not seconds versus minutes, on a table with 1723490 rows and 147190 distinct column values (non-indexed).

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    sorry, I screwed up the first query I posted.

    first, you need a distinct list of values:
    select distinct extents from dba_segments

    next, you need to rank those (i use count):
    select extents, count(extents) over (order by extents) rank
    from (select distinct extents from dba_segments))


    finally, exclose that into a query to get the nth/rank you desire:
    PHP Code:
    select extentsrank from (
        
    select extentscount(extentsover (order by extentsrank
        from 
    (select distinct extents from dba_segments))
    where rank = &nth_highest_value;

    Enter value for nth_highest_value13
    old   4
    where rank = &nth_highest_value
    new   4where rank 13

       EXTENTS       RANK
    ---------- ----------
            
    26         13 
    someone mentioned ascending or descending.
    please augment accordingly as you desire.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  14. #14
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    With regards dense_rank and rank, I would normally use dense_rank unless you have some special case where you need rank.

    As regards RBARAER sql, I find it runs slightly faster than my version on my prod database. However I have learnt from experience that nothing can be taken for granted when it comes to running a query on different databases (dont forget that there are a million and one factors which affect query performance). So make sure you try all the different versions of the sql presented in this thread to see which one is better for YOUR production environment.

    Alan

  15. #15
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One additional point is that the different queries can give slightly different results in certain test cases so make sure you get the behaviour you wish in all cases.

    i.e. the queries will behave differently if you ask for the nth highest row where there are m distinct values and n>m.

    Also check how the queries handle null values.

    Alan

Posting Permissions

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