Fetch random records which definitely contains a given set of records, using MYSQL

Question

I want to use a query just like this; but I know it's not true usage.

In total I need 15 category ids (4,5,6,9,10 and random 10 ids other than the specified)

Tried this

SELECT * 
FROM categories 
WHERE id IN (CONCAT('4,5,6,9,10', SELECT id from categories WHERE id NOT IN(4,5,6,9,10) ORDER BY RAND())) 
LIMIT 0,10

Could you help me to edit this one?

Thanks a lot.

Answer 1

You cannot mix constants and subqueries in an in clause. The following fixes the syntax errors, but doesn't actually solve the problem:

SELECT * 
FROM categories 
WHERE id IN (4,5,6,9,10) or
      id IN (SELECT id from categories WHERE id NOT IN (4,5,6,9,10) ORDER BY RAND()
            ) 
LIMIT 0,15;

You can do what you want this way:

select *
from categories
order by (id in (4, 5, 6, 9, 10)) desc,
      rand()
limit 15;

This will return 10 rows, 5 of which are your desired ids and the rest of which are randomly chosen. (This assumes that id is unique in the categories table.)