mysql query extract multiple records with one query

Question

this is my table

id | question |

id's are grouped as

1000 -> 1050
2000 -> 2030
3000 -> 3099
4000 -> 4500
5000 -> 5010

I have to extract 5 records and one for each group of id.

How can I perform this with only one query?

Thanks a lot.

The easiest solution: Five queries, each of them selecting one (random?) value from one of the given ranges, and making them into one result set via `UNION`. — CBroe, Apr 08 '13 at 13:13
@CBroe thanks, so there is any possibility to do it with only one query? — michele, Apr 08 '13 at 13:14

score 0 · Answer 1 · answered Apr 08 '13 at 13:19

SELECT f1, f2, ... FROM tablename WHERE id BETWEEN 1000 AND 1050 ORDER BY RAND() LIMIT 1
UNION
SELECTS f1, f2, ... FROM tablename WHERE id BETWEEN 2000 AND 2030 ORDER BY RAND() LIMIT 1
UNION
SELECTS f1, f2, ... FROM tablename WHERE id BETWEEN 2000 AND 2030 ORDER BY RAND() LIMIT 1

and two more selects with UNION

score 0 · Answer 2 · answered Apr 08 '13 at 13:26

SELECT *, IF (id > 1000 AND id < 1050, 1, 
          IF (id > 2000 AND id < 2030, 2,
          IF (id > 3000 AND id < 3099, 3,
          IF (id > 4000 AND id < 4500, 4,
          IF (id > 5000 AND id < 5010, 5, 0))))) AS param 
FROM questions WHERE param > 0 GROUP BY param

score 0 · Answer 3 · edited May 23 '17 at 10:25

To do this with just one query (without using UNION as was suggested by NedretRecap), you can add group_id (or similar column) to your table and use simple ... GROUP BY group_id.

Assuming that the reserved group size is always the same (it's not possible that some groups will start at +1000 from previous, and other at +300) you could probably use this simple hack:

SELECT id
FROM table
GROUP BY FLOOR(id/1000)

Working example here.

mysql query extract multiple records with one query

3 Answers3