5 Replies Latest reply: Apr 4, 2013 2:25 PM by 1001096 RSS

    select random rows from table

    1001096
      Hi,
      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

      but it gives an error:

      SQL> select top 10 percent * from employees;

      ORA-00923: FROM keyword not found where expected

      Does anybody know how to select random rows in pl sql?

      thanks

      Edited by: 998093 on 12:11 04/04/2013
        • 1. Re: select random rows from table
          rp0428
          >
          but it gives an error:

          SQL> select top 10 percent * from employees;
          >
          Correct - Oracle does not have or support sql server tables or queries.

          You can delete a given number of rows with a query of the forum
          delete from emp where rownum < 5
          • 2. Re: select random rows from table
            Centinul
            See this in the SQL Reference Manual: Selecting a Sample

            ::EDIT:: I just realized you wanted to delete rows. This will not work with that.

            Edited by: Centinul on Apr 4, 2013 3:19 PM
            • 3. Re: select random rows from table
              JustinCave
              You can use the SAMPLE clause in Oracle.
              SELECT *
                FROM employees SAMPLE( 10 )
              will select roughly 10% of the rows from the table at random.

              Justin
              • 4. Re: select random rows from table
                Frank Kulash
                Hi,
                998093 wrote:
                Hi,
                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
                Did you notice the title at the top of that page?
                <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:

                SQL> select top 10 percent * from employees;

                ORA-00923: FROM keyword not found where expected
                In Oracle you can get approximately 10% of the rows like this:
                SELECT  *
                FROM    employees  SAMPLE (10)
                ;
                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 ...
                Does anybody know how to select random rows in pl sql?
                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
                ;
                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.

                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.
                • 5. Re: select random rows from table
                  1001096
                  Works great, thanks a lot Justin!