Re: Top clause

From: ddf <oratune_at_msn.com>
Date: Fri, 12 Apr 2013 09:12:30 -0700 (PDT)
Message-ID: <25498558-6dea-45f0-b861-71e9aef7f10d_at_googlegroups.com>

On Friday, April 12, 2013 5:19:06 AM UTC-6, happy wrote:
> I can not find that statement works well in oracle 11 sql :
>
> select top 3 * from customers_5;

That's good because Oracle doesn't implement TOP. You have read some of the SQL*Plus documentation on supported commands and functions? You can write a bit longer query in SQL*Plus to return the top n results:

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, of course, is the 'old way'. Oracle 11g has both the RANK() and DENSE_RANK() functions which make that query a bit easier to write and read:

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> As you can see RANK() does the same thing as the older, more complicated query.

What happens if you use DENSE_RANK() instead?

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 so instead of jumping from 1 to 3 (as RANK() did in the previous example) it assigns the next available ranking to all matching values (1,2,2,3 in the case shown above) so you get FOUR rows back with duplicates rather than the expected three.

It's best to learn a new database and its SQL language rather than simply expecting every relational database product to march to the same drummer. As you found out that can be frustrating.

David Fitzjarrell Received on Fri Apr 12 2013 - 18:12:30 CEST