How to do mysql subquery to get random records from pre-filtered list?-CodePudding

I searched but couldn't find a solution that works. Need a bit help here.

Let's say I have a table with more than 100 records, first, I need to find out top 20 records in certain order, then I need to pick 5 randomly from those 20 records. Here is my query,

SELECT a 
FROM tableA 
WHERE b IN (
    SELECT b 
    FROM tableA 
    WHERE c="x" 
    ORDER BY d DESC 
    LIMIT 20
) 
ORDER BY RAND() 
LIMIT 5;

Let me know how to correct it. Thanks.

CodePudding user response：

The inner query selects 20 users ordered by ID and the outer query orders by randome using the RAND() function with a limit of 5 :)

SELECT * FROM 
(
    SELECT * FROM A ORDER BY id desc LIMIT 20
) T1
ORDER BY rand() limit 5