OraFAQ Forum: SQL & PL/SQL » creating a table with only the first 100 records

Home » SQL & PL/SQL » SQL & PL/SQL » creating a table with only the first 100 records

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

creating a table with only the first 100 records [message #8511]

Wed, 27 August 2003 09:22

Andy G
Messages: 25
Registered: May 2003

Junior Member

I'd like to the following cope of a table to my own schema but I want to only include 100 records of that table.....how can I restrict the number of records that get copied over....

Create table schema1.table as select * from schema2.table

thanks

Report message to a moderator

Re: creating a table with only the first 100 records [message #8513 is a reply to message #8511]

Wed, 27 August 2003 09:50

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

-- use rownum
-- here i consider only first 2 rows.

dbadmin@mutation_mutation > select rownum,deptno from dept;

    ROWNUM     DEPTNO
---------- ----------
         1         10
         2         20
         3         30
         4         40

dbadmin@mutation_mutation > create table another_dept as select deptno from dept where rownum < 3;

Table created.

dbadmin@mutation_mutation > select * from another_dept;

    DEPTNO
----------
        10
        20

dbadmin@mutation_mutation >

Report message to a moderator

Re: creating a table with only the first 100 records [message #8515 is a reply to message #8511]

Wed, 27 August 2003 10:20

adelia
Messages: 45
Registered: April 2001

Member

Try this :

select ... where rownum < 101;

Report message to a moderator

Re: creating a table with only the first 100 records [message #8530 is a reply to message #8511]

Thu, 28 August 2003 09:27

sql gurus
Messages: 22
Registered: May 2002

Junior Member

If you want the first 100 records in a specific order
here is the query else it will pick randomly 100 records.

select * from(select rownum rn, col1,col2, from (select col1, col2 ... ,rownum rn,
from table1
order by col1.... ))
where rn < 1

Report message to a moderator

Re: creating a table with only the first 100 records [message #8541 is a reply to message #8511]

Thu, 28 August 2003 13:06

Andy G
Messages: 25
Registered: May 2003

Junior Member

thanks you all this worked perfectly

Report message to a moderator

Previous Topic:	Need some help in transfering data..
Next Topic:	query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 09 01:05:28 CDT 2024