Top clause

[happy]

I can not find that statement works well in oracle 11 sql :
select top 3 * from customers_5;

[ddf]

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

[joel garry]

On Apr 12, 4:19 am, happy <ehabaziz2...@gmail.com> wrote:
> I can not find that statement works well in oracle 11 sql :
> select top 3 * from customers_5;

It helps to have an idea what you really want. Then you can google
for it.

http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_select_the_TOP_N_rows_from_a_table.3F

jg
--
@home.com is bogus.
http://www.forbes.com/sites/oracle/2013/04/12/oracles-software-on-silicon-10-reasons-itll-redefine-enterprise-computing/

[ddf]

Geez, Joel, now you're going to make the guy WORK.


David Fitzjarrell

[Mladen Gogala]

On Fri, 12 Apr 2013 04:19:06 -0700, happy wrote:

> I can not find that statement works well in oracle 11 sql :
> select top 3 * from customers_5;

Your syntax is wrong. It should be "select 'top 3' from customers_5;".
That will definitely work.



--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

[prangel]

You are evil :)

grato.

[Robert Klemme]

On 04/12/2013 10:20 PM, prangel wrote:
> You are evil :)

No, Bert is!

robert

[TheBoss]

prangel <e...@mail.com> wrote in news:kk9q8h$lu8$1...@speranza.aioe.org:

As long as he doesn't work for Google, he is fully entitled.

< http://en.wikipedia.org/wiki/Don%27t_be_evil >

--
Jeroen

[Mladen Gogala]

I'm a DBA, we're supposed to be evil.