Search code examples
phpmysqlrandomsubquery

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

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.

Solution

  • 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