Does oracle SAMPLE work with where filter?

Question

We are usign ORACLE and need to get a random row from a table, satisfying a particular condition. I searched and and found that sample might be useful in this case. However, as I understand Sample returns a random subset of a specified size of the entire table. So in our case, since we need rows that match a particular WHERE condition, it is possible that the query might not return any rows, even if the table contains rows for that filter.

SELECT column FROM ( SELECT column FROM table SAMPLE(10) WHERE COLUMN='abc' ) WHERE rownum = 1

In the above query, if the 10% sample set does not contain any row with column 'abc' it will return empty result (even though the 90% might contain such rows)

Any suggestions to correct this behavior?

You may want to look at this question. SAMPLE doesn't always work as expected. You'll probably want to use the rownumber method in Alex's answer, to ensure that you always get a random row. — Jon Heller
Not sure what is the problem here... The column='abc' may be 7%, not 10. This is why you get null in result. — Art

Tony Andrews Tony Andrews · Accepted Answer · 2013-04-23T16:05:12

Try this:

WITH data AS (SELECT column FROM table WHERE COLUMN='abc')
SELECT column FROM data SAMPLE(10) 
WHERE rownum = 1

Does oracle SAMPLE work with where filter?

1 Answers