2nd value??

irontree23 · #1 (**permalink**) 01-20-06, 04:31

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?

Littlefoot · #2 (**permalink**) 01-20-06, 05:37

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).

joebednarz · #3 (**permalink**) 01-20-06, 11:56

You can do a traditional Top-N analysis query:

Code:

SELECT ename, sal
FROM (select ename, sal order by sal desc)
WHERE rownum = 2;

RBARAER · #4 (**permalink**) 01-20-06, 12:43

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

JMartinez · #5 (**permalink**) 01-20-06, 13:09

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*

beilstwh · #6 (**permalink**) 01-20-06, 13:45

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

irontree23 · #7 (**permalink**) 01-20-06, 15:52

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

RBARAER · #8 (**permalink**) 01-22-06, 11:48

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

Internet Services

Web Development

Digital Marketing

Consumer Tech