3

Here's a fiddle

I have a simple table looking like this :

USER_NAME   STATUS
------------------
Paul        1
John        4
Brad        1
Simon       1
Jack        4

I have 10 different status and around 2000 rows (users).

I want to export a small sample of rows from each status from my production database. Then I will import them in my dev database to run tests

I want a query that select a few (let's say 3) rows for each value of the status field

  • 3 users with status 1
  • 3 users with status 2
  • 3 users with status 3
  • ...

How could I achieve that ? Do i have to do a loop on STATUS and use LIMIT ?

Thanks a lot !

EDIT : edited for clarity

Community
  • 1
  • 1
user3316439
  • 65
  • 1
  • 8
  • This will do what you want... SELECT * FROM users; – Strawberry Mar 12 '14 at 17:45
  • Sorry, I'm not very clear : I don't care which 3, I will have like 10 different status and I want to export a sample of each status from my production database. Then I will import them in my dev database to run tests – user3316439 Mar 12 '14 at 17:48

4 Answers4

2

For this size of data, it might be easiest to use union all:

(select t.* from table t where status = 1 order by rand() limit 3)
union all
(select t.* from table t where status = 2 order by rand() limit 3)
union all
. . .
union all
(select t.* from table t where status = 10 order by rand() limit 3)

There are other methods that would perform better. But this should be quite reasonable on the volume of data you have.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    I like your answer but I don't really care about performances. I'm more interested in a simple query I can store somewhere and reuse and adapt to other cases. If somedaw I have a similar case but with more status this method wouldn't be appropriate – user3316439 Mar 12 '14 at 18:30
1

You can try this query for a single status, you can extend it by changing the number though...

SELECT * FROM tbl_name JOIN users ON ( users.Status = status.Id ) WHERE status.Id = 1 GROUP BY status LIMIT 3
Tosfera
  • 512
  • 3
  • 14
0

try this query

SELECT users FROM table WHERE status=max(status) ORDER status LIMIT BY 3

vignesh pbs
  • 418
  • 2
  • 5
  • 16
0

Taken from here:

SELECT a.user_name, a.status
FROM table AS a
LEFT JOIN table AS b
    ON a.status = b.status
    AND a.created_at <= b.created_at
GROUP BY a.id
HAVING count(*) <= 3
ORDER BY a.status

Tested on MySQL 5.5

Community
  • 1
  • 1
e18r
  • 7,578
  • 4
  • 45
  • 40