On Friday, April 12, 2013 9:18:46 AM UTC-6, Michael Moore wrote: > > select * from customers_5 where rownum < 4; > > > On Fri, Apr 12, 2013 at 4:17 AM, Serious_Developer > <ehabaz...@gmail.com<javascript:> > > wrote: > >> I can not find that statement works well in oracle 11 sql : >> select top 3 * from customers_5; >> >> -- >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to oracle...@googlegroups.com<javascript:> >> To unsubscribe from this group, send email to >> oracle-plsql...@googlegroups.com <javascript:> >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en >> >> --- >> You received this message because you are subscribed to the Google Groups >> "Oracle PL/SQL" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to oracle-plsql...@googlegroups.com <javascript:>. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> > Depends on what you want, really:
SQL> select empno, ename, sal 2 from 3 (select empno, ename, sal, rownum rn 4 from 5 (select empno, ename, sal 6 from emp 7 order by sal desc)) 8 where rn <= &1; Enter value for 1: 3 old 8: where rn <= &1 new 8: where rn <= 3 EMPNO ENAME SAL ---------- ---------- ---------- 7839 KING 5000 7902 FORD 3000 7788 SCOTT 3000 SQL> That query gives you the top three salary earners in the company but it's kind of old-school. We can re-write that using RANK() and get a cleaner query: SQL> select empno, ename, sal 2 from 3 (select empno, ename, sal, 4 rank() over (order by sal desc) rk 5 from emp) 6 where rk<= &1; Enter value for 1: 3 old 6: where rk<= &1 new 6: where rk<= 3 EMPNO ENAME SAL ---------- ---------- ---------- 7839 KING 5000 7788 SCOTT 3000 7902 FORD 3000 SQL> And it gives the same results. Unless you know what you're doing and what to expect using DENSE_RANK() in place of RANK() can provide some surprises: SQL> select empno, ename, sal 2 from 3 (select empno, ename, sal, 4 dense_rank() over (order by sal desc) rk 5 from emp) 6 where rk<= &1; Enter value for 1: 3 old 6: where rk<= &1 new 6: where rk<= 3 EMPNO ENAME SAL ---------- ---------- ---------- 7839 KING 5000 7788 SCOTT 3000 7902 FORD 3000 7566 JONES 2975 SQL> DENSE_RANK() doesn't skip ranking numbers when duplicates are found, it assigns all of the duplicate values the same sequential ranking (1,2,2,3 in this case) so the query returns FOUR rows, not three. David Fitzjarrell -- -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en --- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To unsubscribe from this group and stop receiving emails from it, send an email to oracle-plsql+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.