Re: Whats wrong with this query ?? Top 5

In article <fa4781e4.0111282251.5c14466c_at_posting.google.com>, mark_brehmen_at_yahoo.com says...
>
>Usual method of selecting Top 5 highest paid employees is
>
>select ename ,sal from (select ename,sal from emp
>order by Sal Desc) where rownum <6;
>
>
>I saw the below query somewhere else in some book which also seems to work fine
>Anything wrong with this?. Why is this query not mentioned in Jonanthans FAQ?
>This works with almost all oracle versions. I like the elegance of the below
>one.
>
>select ename, sal from emp A where 5 >
>(select count(*) from emp B where B.Sal > A.Sal) order by sal desc;
>
>Regards
>Mark

your query returns more then 5 rows.

scott_at_ORA8I.WORLD> update emp set sal = 100;

14 rows updated.

scott_at_ORA8I.WORLD>
scott_at_ORA8I.WORLD> select ename, sal from emp A where 5 >   2 (select count(*) from emp B where B.Sal > A.Sal) order by sal desc;

ENAME SAL
---------- ----------

SMITH             100
ALLEN             100
WARD              100
JONES             100
MARTIN            100
BLAKE             100
CLARK             100
SCOTT             100
KING              100
TURNER            100
ADAMS             100
JAMES             100
FORD              100
MILLER            100

14 rows selected.

scott_at_ORA8I.WORLD>
scott_at_ORA8I.WORLD> rollback;

Rollback complete.

It returns the set of people who make the top five salaries.

Your query does tons of work to get the answer -- for every row, it must run that subquery, count and compare.

The rownum query can read the index backwards, get 5 entries and return. Its blazing fast compared to the correlated subquery.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp