Oracle - select random rows with potential duplicates

Question

I am using Oracle 11gR2. Given a table, I would like to return a certain number of rows in random order, with potential duplicates.

All the posts I have seen (here or here or here also) are about finding a number of unique rows in random order.

For example, given this table and asking for 2 random rows:

Table
-----------------
ID  LABEL
1   Row 1
2   Row 2
3   Row 3

I would like the query to return

1   Row 1
2   Row 2

but also possibly

1   Row 1
1   Row 1

How could this be done using only pure SQL (no PL/SQL or stored procedure) ? The source table does not have duplicate rows; by duplicate, I mean two rows having the same ID.

http://psoug.org/snippet/SELECT-with-SAMPLE-clause_661.htm – abhi Mar 03 '15 at 17:13 — abhi, Mar 03 '15 at 17:13

score 2 · Answer 1 · answered Mar 03 '15 at 17:28

select a random row union select another random row

That gives you two totally randomized rows, which can be the same, if both randoms have the same value, or two different rows. The key is to do two random selects, not one to return two rows

If you want more than two rows, i think the best solution would be to have a random-number-table, do a full outer join to that table and order by random, select top(n) of that join. By the full outer join you have each Row of your Sourcetable many times in the result set before selecting the top(n)

score 2 · Accepted Answer · answered Mar 03 '15 at 17:32

Maybe something like this (where p_num is a parameter):

with sample_data as (select 1 id, 'row 1' label from dual union all
                     select 2 id, 'row 2' label from dual union all
                     select 3 id, 'row 3' label from dual),
           dummy as (select level lvl
                     from   dual
                     connect by level <= p_num)
select *
from   (select sd.*
        from   sample_data sd,
               dummy d
        order by dbms_random.value)
where  rownum <= p_num;

I really wouldn't like to use this in production code, though, as I don't think it will scale at all well.

What's the reasoning behind your requirement? It doesn't sound like particularly good design to me.

score 0 · Answer 3 · answered Mar 03 '15 at 17:16

I cant think of any way to do it without a stored procedure.

You might be able to make sue of DBMS_RANDOM

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_random.htm#i998925

http://www.databasejournal.com/features/oracle/article.php/3341051/Generating-random-numbers-and-strings-in-Oracle.htm

You could generate a random primary key and return that?

score 0 · Answer 4 · answered Mar 03 '15 at 18:17

0

You can use DBMS_RANDOM in a SQL Query.

SELECT ID FROM
(
  SELECT ID FROM mytable
ORDER BY dbms_random.value)
WHERE ROWNUM <=2

http://www.sqlfiddle.com/#!4/c6487/13/0

answered Mar 03 '15 at 18:17

abhi

3,082
6
47
73

Oracle - select random rows with potential duplicates

4 Answers4