Your answers are always great David, but all your effort makes me feel a little guilty about being lazy in comparison. :-)
On Fri, Apr 12, 2013 at 2:17 PM, ddf <orat...@msn.com> wrote: > > > On Friday, April 12, 2013 11:36:43 AM UTC-6, Michael Moore wrote: > >> 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 <ora...@msn.com> 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 <ehabaz...@gmail.com >>>> > 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 >>>>> >>>>> 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/**grou**ps/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 oracle...@googlegroups.com >>> 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> >>> . >>> >>> >>> >> >> I wasn't answering you as I was answering the same guy with a different > 'name' who's trolling the oracle newsgroups. > > But, I've always been wordy. > > > 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. > > > -- -- 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.
