I need to return a short list of products. I want featured items first in random order, and then non-featured items making up any shortfall, also in random order.
I have got this statement but while the two separate SELLECT statements work individually when in the UNION they always bring back the same "Random" records
SELECT p1.*
FROM ( SELECT ID, Name, Featured
FROM Product
WHERE Featured = 1
ORDER BY RAND()
) p1
UNION
SELECT p2.*
FROM ( SELECT ID, Name, Featured
FROM Product
WHERE Featured = 0
ORDER BY RAND()
) p2
LIMIT 0,6
2
Answers
Try the following Query: