This content has been marked as final. Show 5 replies
998093 wrote:Did you notice the title at the top of that page?
I want to delete 100 random rows from a table.
I saw this solution: http://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table
<h2>Select n random rows from SQL Server table</h2>
Oracle and SQL Server are separate products. Not everything that works in one will ncessarily work in the other.
but it gives an error:In Oracle you can get approximately 10% of the rows like this:
SQL> select top 10 percent * from employees;
ORA-00923: FROM keyword not found where expected
There's no guarantee you will get exactly 10%. If the table has exactly 1000 rows, then you might get 100 rows in the result set, but then again you might get 99, or 101, or 102, or 98, or ...
SELECT * FROM employees SAMPLE (10) ;
Does anybody know how to select random rows in pl sql?
This is guaranteed to get exactly 100 rows (or all the rows, if the table has fewer than 100). You don't need to know how many rows are in the table.
WITH got_r_num AS ( SELECT e.* -- or whatever columns you want , ROW_NUMBER () OVER (ORDER BY dbms_random.value) AS r_num FROM employees e ) SELECT * -- or list all columns except r_num FROM got_r_num WHERE r_num <= 100 ;
The query above works in pure SQL; you don't need PL/SQL.
Of course, if you need to do this in PL/SQL for some reason, then you can.