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]<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 [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.
>>
>>
>>
> 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.
