Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How can I grab random rows from database based on the key value?

For example if I have a ‘customers’ database similar to:

state customer
Alabama Adam
Alabama Aaron
Oklahoma Randy
California Sam
California Darren

And I want to grab 10 random customers from each state, how would I go about accomplishing this?
I could do a union, ie:

(SELECT * FROM customers WHERE state = 'Alabama' LIMIT 10)
UNION ALL
(SELECT * FROM customers WHERE state = 'Alaska' LIMIT 10)
UNION ALL
(SELECT * FROM customers WHERE state = 'Arizona' LIMIT 10)
UNION ALL
(SELECT * FROM customers WHERE state = 'Arkansas' LIMIT 10)
...

But that seems really inefficient (basically 50 selects).
I could grab the entire database and then parse it after the fact, but that could also get pretty costly. Surely there’s a better way?

Tags:

2

Answers


  1. 0

    Use row_number window function without specifying ORDER BY to get a somewhat arbitrary result:

    select customer, state
from (
  select c.*, row_number() over (partition by state) rn
  from customers c
) dt
where rn <= 10
order by state, customer;

    Demo at https://dbfiddle.uk/M7hHHVyC

    Login or Signup to reply.
  2. 0

    Number the rows per state in random order, then keep all rows numbered 1 to 10:

    SELECT state, customer
FROM
(
  SELECT
    state, 
    customer,
    row_number() over(partition by state order by rand()) as rn
  FROM customers 
) c
WHERE rn <= 10
ORDER BY state, customer;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search