Solved

select xyz records from an oracle table

Posted on 2014-02-18
5
625 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
ID: 39866984
select *
from sometable
where rownum <= 30
0
 
LVL 28

Assisted Solution

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

This will give you the 30 records
0
 
LVL 1

Expert Comment

by:Lacca
ID: 39867014
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)
ID: 39867296
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
ID: 39869127
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Exchange 2013 free and busy between mailbox databases 77 98
ODBC in excel 2016 in Windows 10 via VBA 16 83
Mongo DB 18 45
MySQL Backup Strategy 15 23
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

773 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