Sometimes I give a terse answer that may, or may not be correct, depending on the meaning of the question. If I am wrong, it forces the asker to clarify the question. ;-)
On Fri, Apr 12, 2013 at 9:19 AM, ddf <[email protected]> wrote: > > > 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 >> <[email protected]>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 [email protected] >>> >>> To unsubscribe from this group, send email to >>> Oracle-PLSQL...@**googlegroups.com >>> >>> For more options, visit this group at >>> http://groups.google.com/**group/Oracle-PLSQL?hl=en<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. >>> >>> For more options, visit >>> https://groups.google.com/**groups/opt_out<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 [email protected] > To unsubscribe from this group, send email to > [email protected] > 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 [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
