OraFAQ Forum: SQL & PL/SQL » Sample Extraction

Home » SQL & PL/SQL » SQL & PL/SQL » Sample Extraction

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

Sample Extraction [message #569746]

Thu, 01 November 2012 00:59

srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad

Member

Hi all

in my table i have 10,00,000 rows in that i have to extract company wise 5000 records...

SELECT distinct * FROM Table_name sample(.5) where Company_Code='24'

is i'm in right way or not

can you guide me please

Thanks and regards

Report message to a moderator

Re: Sample Extraction [message #569747 is a reply to message #569746]

Thu, 01 November 2012 01:19

tigsav
Messages: 27
Registered: April 2012

Junior Member

Hi

Please post a working test case along with any error recieved as it is from an sqlplus session.
Also from your query i am not able to comprehend anything.Please put up a detailed explanation.

Thanks,
tigsav

Report message to a moderator

Re: Sample Extraction [message #569749 is a reply to message #569746]

Thu, 01 November 2012 01:37

Michel Cadot
Messages: 54140
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

i have to extract company wise 5000 records

select * from mytable where rownum<=5000;

Regards
Michel

Report message to a moderator

Re: Sample Extraction [message #569751 is a reply to message #569749]

Thu, 01 November 2012 01:41

srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad

Member

thanks for your reply

SELECT distinct * FROM Table_name sample(.5) where Company_Code='24' union
SELECT distinct * FROM Table_name sample(.5) where Company_Code='136'

i need sample records extarcted by Oracle "Sample" function,not rownum wise

Thanks and regards

Report message to a moderator

Re: Sample Extraction [message #569753 is a reply to message #569751]

Thu, 01 November 2012 02:01

Michel Cadot
Messages: 54140
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

i need sample records extarcted by Oracle "Sample" function,not rownum wise

Why? How does this answer the original question: the one I quoted?
If you have the answer you want why do you post a question?

Note that SAMPLE clause is described in details in Database SQL Reference. What do you need from us?

Regards
Michel

Report message to a moderator

Re: Sample Extraction [message #569755 is a reply to message #569753]

Thu, 01 November 2012 02:23

srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad

Member

Sorry Michel,

i posted question because every time i run query using Sample function...

it extracting either 5116 records or 4956 but not exact 5000 records

i want randomly rows been to select.

thanks and regards

Report message to a moderator

Re: Sample Extraction [message #569756 is a reply to message #569755]

Thu, 01 November 2012 02:25

Michel Cadot
Messages: 54140
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

it extracting either 5116 records or 4956 but not exact 5000 records

Yes, it is designed as this, this is documented.

Quote:

i want randomly rows been to select.

So you need ROWNUM but if you don't want to use it (or one of its variation like ROW_NUMBER...), you can't do it.

Regards
Michel

Report message to a moderator

Re: Sample Extraction [message #569757 is a reply to message #569756]

Thu, 01 November 2012 02:27

srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad

Member

Thanks

Report message to a moderator

Re: Sample Extraction [message #569758 is a reply to message #569757]

Thu, 01 November 2012 02:30

Michel Cadot
Messages: 54140
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You are not interesting in a solution using ROWNUM (which is not what I posted)?
If not, so how will you achieve your tasks?

Regards
Michel

Report message to a moderator

Re: Sample Extraction [message #569761 is a reply to message #569758]

Thu, 01 November 2012 02:44

srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad

Member

i will create temporary table by using Sample function,
after that using rownum i will select my desired output.

Thanks and Regards

Report message to a moderator

Re: Sample Extraction [message #569763 is a reply to message #569761]

Thu, 01 November 2012 02:54

Michel Cadot
Messages: 54140
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You don't need a temporary table, the following query do it:

select * from (select * from mytable order by dbms_random.value) where rownum <= 5000;

Or even if you want to use the SAMPLE clause.

select * from (select * from mytable sample(.5)) where rownum <= 5000;

Regards
Michel

Report message to a moderator

Previous Topic:	ORA-00997: illegal use of LONG datatype
Next Topic:	Transpose

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 20 08:06:19 CDT 2013

Total time taken to generate the page: 0.08299 seconds