hi to all masters
please go through both the given queries...

SELECT * FROM EMP A WHERE 1=(SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE B.SAL>A.SAL);
SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM (SELECT DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=2);

both queries will fetch second highest sal from emp table.
i would like to know that which one is better and why please give reason.
my other question is: which sorting is used by oracle in order by and group by clause.
thank you

Hi Vinod,

<< i would like to know that which one is better and why please give reason.

Great question, Vinod!

What is your dfeinition iof "Better"?

- The one that runs fastest?

- The one with the most throughput?

- The one that uses the least computing resources?

Please post the execution plans and let's take a look and see.

The second query concerns me because it is using ROWNUM and that may not guarantee the 2nd row every tine you runn the 2nd row query. See these tips on finding the first N rows in a table:

http://www.dba-oracle.com/t_top_n_sql.htm

***************************************
>> which sorting is used by oracle in order by and group by clause

We need to see the execution plan to find out!

Also set timing on and run the queries to see which one return the 2nd highest value the fastest.

Follow these easy steps to use autotrace:

http://www.dba-oracle.com/t_OracleAutotrace.htm

To learn a full procedure for ptimizing SQL, try the code downlkad in my book "Oracle Tuning: The Definitive Reference":

http://rampant-books.com/book_1002_oracle_...ence_2nd_ed.htm




http://www.dba-oracle.com/t_2nd_highest_row_value_sql.htm