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 <[email protected] <javascript:>> 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]<javascript:> >> To unsubscribe from this group, send email to >> [email protected] <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 [email protected] <javascript:>. >> For more options, visit 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 [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.
