Results 1 to 8 of 8

Thread: 2nd value??

  1. #1
    Join Date
    Jan 2006
    Posts
    11

    Question Unanswered: 2nd value??

    hai,
    how to select 2nd largest value and the 2nd smallest values from a column records in a table?

    also how do we find out how much space is used by a table in a database?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Second largest value could be found using such a query:
    Code:
    SELECT ename, sal
    FROM (SELECT ename, sal, rank() over (ORDER BY sal DESC) salary_rank
          FROM EMP)
    WHERE salary_rank = 2;
    To find the second smallest value, just remove red-coloured DESC (or rename it to ASC).

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    You can do a traditional Top-N analysis query:

    Code:
    SELECT ename, sal
    FROM (select ename, sal order by sal desc)
    WHERE rownum = 2;
    JoeB
    save disk space, use smaller fonts

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by joebednarz
    You can do a traditional Top-N analysis query:

    Code:
    SELECT ename, sal
    FROM (select ename, sal order by sal desc)
    WHERE rownum = 2;
    You're right, but in terms of performance I would clearly bet on Littlefoot's version using the Rank() analytic function. Analytic functions are generally excellent for such queries. Just my 2 cents .

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Performance-wise, analytics could be dangerous some times. This is a perfect example. Litlefoot's query, will have to first select all of the rows from the EMP table in order to apply the WHERE on the analytic itself. I would say, go for it, if the table is relatively *small*. If its not, make sure you have an index on the column you're wanting to WHERE on, and do something like select * from table where id = ( select max( id ) from table where id <> ( select max( id ) from table ) ).

    BTW, joebednarz's query is *wrong*

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by joebednarz
    You can do a traditional Top-N analysis query:

    Code:
    SELECT ename, sal
    FROM (select ename, sal order by sal desc)
    WHERE rownum = 2;
    Unfortunally your query would not work. Rownum refers to the number of the row returned. "rownum = 2" will never return anything.

    SQL> select object_name
    2 from all_objects
    3 where rownum = 1;

    OBJECT_NAME
    ------------------------------
    DUAL

    SQL> edit
    Wrote file afiedt.buf

    1 select object_name
    2 from all_objects
    3* where rownum = 2
    SQL> /

    no rows selected


    Your query could be rewritten as follows

    SQL> edit
    Wrote file afiedt.buf

    1 select object_name
    2 from
    3 (select object_name,rownum rnum
    4 from (select object_name
    5 from all_objects
    6 order by object_name desc))
    7* where rnum = 2
    SQL> /

    OBJECT_NAME
    ------------------------------
    sun/tools/util/CommandLine
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jan 2006
    Posts
    11

    thanks

    Quote Originally Posted by Littlefoot
    Second largest value could be found using such a query:
    Code:
    SELECT ename, sal
    FROM (SELECT ename, sal, rank() over (ORDER BY sal DESC) salary_rank
          FROM EMP)
    WHERE salary_rank = 2;
    To find the second smallest value, just remove red-coloured DESC (or rename it to ASC).


    that helped a lot
    thanks

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by JMartinez
    BTW, joebednarz's query is *wrong*
    You're right, sorry for that one .

    Thank you for pointing it out and thanks to beilstwh for correcting it.

    Usually I test before saying something, even if I'm "sure" about it. Here I didn't have much time, didn't test... and made a mistake !

    As for analytics performance, this is not a case in which they will perform best, as you said. They do when they are used upon a small part of a table, not the whole table. Yet it's always good to think about them, test and see, don't you think ?

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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