Is there a simple way to pick random records (about 20) from a data table with specific condition

Question

Here is the code I used

SELECT * FROM   
(SELECT * FROM [table name] 
ORDER BY dbms_random.value)
WHERE rownum < 10

I would like to select random rows only from records with column A = XYZ. I tried to add a WHERE clause in the code:

SELECT * FROM   
(SELECT * FROM [table name] 
WHERE [column A] = 'XYZ'
ORDER BY dbms_random.value)
WHERE rownum < 10

but got an error. Any feedback would be greatly appreciated.

are you able to run a simple query that returns all `A = XYZ` records? — jsotola, Mar 20 '20 at 04:32
it doesn't look too odd. but without the error text, we're just guessing. My guess is that the current user is not allowed to access dbms_random.value. — increddibelly, Mar 20 '20 at 06:11
Your first query would not work in SQL Server. Tag the question with the database that you are really using. — Gordon Linoff, Mar 20 '20 at 12:11

Venkataraman R · Answer 1 · 2020-03-20T04:34:50.160

2

You can try the below approach in SQL Server:

SELECT TOP 10 * FROM [table name] 
WHERE [column A] = 'XYZ'
ORDER BY newid()

More information on how ORDER BY newid() works in a SO post

edited Mar 20 '20 at 04:34

answered Mar 20 '20 at 04:31

Venkataraman R

12,181
2
31
58

also could be very inefficient. question has been asked several times before – Mitch Wheat Mar 20 '20 at 04:51

Gordon Linoff · Answer 2 · 2020-03-22T14:37:32.877

0

Your code looks like Oracle code. It does not recognize square braces for identifiers. In fact, don't escape them -- or use double quotes if you must:

SELECT t.*   
FROM (SELECT t.*
      FROM t 
      WHERE columnA = 'XYZ'
      ORDER BY dbms_random.value
     ) t
WHERE rownum < 10;

In SQL Server, the equivalent would be:

SELECT TOP (10) t.*
FROM t 
WHERE columnA = 'XYZ'
ORDER BY NEWID()

edited Mar 22 '20 at 14:37

answered Mar 20 '20 at 12:13

Gordon Linoff

1,242,037
58
646
786

@MitchWheat . . . The OP claims that s/he used the first query, presumably with no error. That strongly suggests that the OP is not using SQL Server. – Gordon Linoff Mar 22 '20 at 14:38

Is there a simple way to pick random records (about 20) from a data table with specific condition

2 Answers2