Solved

select xyz records from an oracle table

Posted on 2014-02-18
5
610 Views
Last Modified: 2014-02-18
is there an easy way within oracle (sql*plus) to run a select clause to only select say a sample of 30 rows per database table.

i.e. select (sample of 30)  * (all fields)
from mytable
0
Comment
Question by:pma111
5 Comments
 
LVL 20

Accepted Solution

by:
chaitu chaitu earned 250 total points
Comment Utility
select *
from sometable
where rownum <= 30
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 250 total points
Comment Utility
select * from your_table
where rownum < 31;

This will give you the 30 records
0
 
LVL 1

Expert Comment

by:Lacca
Comment Utility
To see the number of the row use:

select rownum, a.*
from mytable a
where rownum < 31
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Even though you already selected an answer to this, you asked for a 'sample' of data.  The accepted answers return the first 30 rows you select.  This really isn't a sample.

There is a SAMPLE clause in sqlplus that you pass a percentage.

There is also this trick (still uses rownum but against a random order):
select * from
( select * from some_table  order by dbms_random.value)
where rownum<=30
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
my understanding was that a random sample set of records was not requested to be output each time the query runs otherwise i would have suggested the use of the dbms_random package stuff. Thanks,
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now