Home » SQL & PL/SQL » SQL & PL/SQL » Sample Extraction
Sample Extraction [message #569746] Thu, 01 November 2012 00:59 Go to next message
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
Re: Sample Extraction [message #569747 is a reply to message #569746] Thu, 01 November 2012 01:19 Go to previous messageGo to next message
tigsav
Messages: 41
Registered: April 2012
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
Re: Sample Extraction [message #569749 is a reply to message #569746] Thu, 01 November 2012 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59290
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
Re: Sample Extraction [message #569751 is a reply to message #569749] Thu, 01 November 2012 01:41 Go to previous messageGo to next message
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
Re: Sample Extraction [message #569753 is a reply to message #569751] Thu, 01 November 2012 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 59290
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
Re: Sample Extraction [message #569755 is a reply to message #569753] Thu, 01 November 2012 02:23 Go to previous messageGo to next message
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
Re: Sample Extraction [message #569756 is a reply to message #569755] Thu, 01 November 2012 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59290
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
Re: Sample Extraction [message #569757 is a reply to message #569756] Thu, 01 November 2012 02:27 Go to previous messageGo to next message
srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad
Member
Thanks Smile
Re: Sample Extraction [message #569758 is a reply to message #569757] Thu, 01 November 2012 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59290
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
Re: Sample Extraction [message #569761 is a reply to message #569758] Thu, 01 November 2012 02:44 Go to previous messageGo to next message
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
Re: Sample Extraction [message #569763 is a reply to message #569761] Thu, 01 November 2012 02:54 Go to previous message
Michel Cadot
Messages: 59290
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

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


Current Time: Wed Oct 01 09:51:27 CDT 2014

Total time taken to generate the page: 0.14123 seconds