OraFAQ Forum: SQL & PL/SQL » simply need top n rows as in table

Home » SQL & PL/SQL » SQL & PL/SQL » simply need top n rows as in table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

simply need top n rows as in table [message #141216]

Fri, 07 October 2005 09:30

bornlooser
Messages: 20
Registered: September 2005
Location: Jakarta

Junior Member

Hi All,

Just i wants to select top n rows as they are physically stored in database. No top 5 salary etc.
in know in SQL server :
select top 5 * from emp , same i wants in oracle.

thanks
Bonny

Report message to a moderator

Re: simply need top n rows as in table [message #141217 is a reply to message #141216]

Fri, 07 October 2005 09:35

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

rownum....

select * from table where rownum <= 5;

Report message to a moderator

Re: simply need top n rows as in table [message #141232 is a reply to message #141217]

Fri, 07 October 2005 11:32

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Just a clarification though - rownum will not necessarily return the rows as they are "physically stored" or in the order they were entered.

Report message to a moderator

Re: simply need top n rows as in table [message #141235 is a reply to message #141216]

Fri, 07 October 2005 11:48

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Good point...it won't be a first in first out sort of thing.

I was assuming it meant a "random" set of rows, without explicit ordering.

Report message to a moderator

Re: simply need top n rows as in table [message #141275 is a reply to message #141216]

Fri, 07 October 2005 23:46

anusuya123
Messages: 2
Registered: October 2005

Junior Member

hi,
to select top 5 rows as they are physically stored in database :
select * from emp where rownum <= 5;

rownum always corresponds to the rows in the order they were entered..... try inserting rows and see for yourself.

Report message to a moderator

Re: simply need top n rows as in table [message #141334 is a reply to message #141275]

Sun, 09 October 2005 03:25

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Absolutely totally wrong. Rownum has nothing to do with the order of insertion.

SQL> create table t ( x int, a char(2000), b char(2000), c char(2000) );
Table created.

SQL> insert into t (x,a,b,c) values ( 1, 'x', 'x', 'x' );
1 row created.

SQL> insert into t (x,a,b,c) values ( 2, 'x', 'x', 'x' );
1 row created.

SQL> insert into t (x,a,b,c) values ( 3, 'x', 'x', 'x' );
1 row created.

SQL> delete from t where x = 2;
1 row deleted.

SQL> insert into t (x,a,b,c) values ( 4, 'x', 'x', 'x' );
1 row created.

SQL> select x from t where rownum <= 4;

         X
----------
         1
         4
         3

Report message to a moderator

Re: simply need top n rows as in table [message #141430 is a reply to message #141216]

Mon, 10 October 2005 04:33

sudhir.sukumar
Messages: 52
Registered: August 2005
Location: India

Member

Quote:

Just i wants to select top n rows as they are physically stored in database.

scott@TESTDB> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
       414 SUDHIR     ASSISTANT       7902 17-SEP-03        100         10         20

15 rows selected.

scott@TESTDB> select * from
  2  (select rownum as rn, group_of_rec.* from
  3  (select * from emp) group_of_rec
  4  where rownum <=5)
  5  where rn >=1
  6  /

        RN      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
         1       7369 SMITH      CLERK           7902 17-DEC-80        800                    20
         2       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
         3       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
         4       7566 JONES      MANAGER         7839 02-APR-81       2975                    20
         5       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

scott@TESTDB>

Report message to a moderator