Home » SQL & PL/SQL » SQL & PL/SQL » top 10 rows
top 10 rows [message #207278] Mon, 04 December 2006 22:58 Go to next message
sujeethbala
Messages: 25
Registered: October 2006
Location: INDIA
Junior Member
hi
i have a small doubt. in sql server to get the top/first few rows i use 'select Top 10 * from table1'. what it is the equilant in oracle. please reply.

thank you
sujith
Re: top 10 rows [message #207282 is a reply to message #207278] Mon, 04 December 2006 23:10 Go to previous messageGo to next message
inforacle
Messages: 21
Registered: December 2006
Junior Member

select * from <tablename> where Rownum <11;

this query gives u top 10 rows
Re: top 10 rows [message #207284 is a reply to message #207282] Mon, 04 December 2006 23:14 Go to previous messageGo to next message
sujeethbala
Messages: 25
Registered: October 2006
Location: INDIA
Junior Member
thank you
Re: top 10 rows [message #207285 is a reply to message #207278] Mon, 04 December 2006 23:21 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,
you can also use "order by" clause.
like
select * from tab_name order by [asc][desc]

ascending order is default.
you use order by clause last in ur sql statement.

regards
Taj
Re: top 10 rows [message #207308 is a reply to message #207285] Tue, 05 December 2006 00:34 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm, right, but this will return 10 rows only if tab_name has exactly 10 rows Smile
Re: top 10 rows [message #207311 is a reply to message #207285] Tue, 05 December 2006 00:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I am afraid things are not as straightforward as they appear to be. If you use "where rownum <= n" you will get n random rows from the resultset, NOT the top n.
Even if you would add an order by, it would first execute the where-clause (including the rownum <= n) and after that perform the order by, thus ordering the n random rows.
A way to get the top n rows is this:
select col1
,      col2
from   (select col1
        ,      col2
        ,      row_number() over (order by col1 desc) as rn
        from   tab1
       )
where  rn <= n


If you search the forum you will find plenty of other examples as well.

Example:
SQL> drop table faq;

Table dropped.

SQL> create table faq
  2  as
  3    select trunc(dbms_random.value * 200) as id
  4    from   all_objects
  5    where  rownum <= 1000;

Table created.

SQL> REM This is wrong!
SQL> select id
  2  from   faq
  3  where  rownum <= 10;

        ID
----------
        50
       146
        82
       187
       144
         8
        86
       103
        57
         5

10 rows selected.

SQL> REM Add an order by:
SQL> select id
  2  from   faq
  3  where  rownum <= 10
  4  order  by id desc;

        ID
----------
       187
       146
       144
       103
        86
        82
        57
        50
         8
         5

10 rows selected.

SQL> REM This is right:
SQL> select *
  2  from   (select id
  3  	     ,	    row_number() over (order by id desc) as rn
  4  	     from   faq
  5  	    )
  6  where  rn <= 10;

        ID         RN
---------- ----------
       199          1
       199          2
       199          3
       199          4
       198          5
       198          6
       198          7
       198          8
       198          9
       198         10

10 rows selected.
Re: top 10 rows [message #207562 is a reply to message #207278] Wed, 06 December 2006 00:55 Go to previous message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
After creating id, as our senior had shown to us. one more query can give u top 10 rows.

SQL> select * from (select id from faq order by id desc) where rownum<=10;

ID
---------
199
199
199
199
199
198
198
198
198
198

10 rows selected.
Previous Topic: to check the datatype of argument passed inside procedure
Next Topic: max no. of columns
Goto Forum:
  


Current Time: Fri Dec 09 07:44:34 CST 2016

Total time taken to generate the page: 0.11001 seconds