How to get a sample in Oracle SQL

Question

I have the following query:

Select c.name, c.age, m.salary from customers c
left join money m on c.ID = m.ID and m.salary > 10000

where c.age > 50

Now I would like to get a sample of 100 rows. How can I do this? I tried it with fetch first 100 rows only, but this yields not a random sample.

Answer 1

You can do a random sort before fetching:

Select c.name, c.age, m.salary
from customers c left join
     money m
     on c.ID = m.ID and m.salary > 10000
where c.age > 50
order by dbms_random.random()
fetch first 100 rows only;