Ankur Mahajan Ankur Mahajan - 2 months ago 7
SQL Question

MySql - Get random records based on the column value

I have a sql table,

Employee
which has 4 columns.


  • Id

  • Name

  • Address

  • Status – Fixed Type (Enum). Having 3 fixed values –
    OPEN
    ,
    CLOSED
    and
    PENDING
    .



Let’s assume Total Records in the table are 200.

Now, I want to form a query which would return -


  • 50 random records which have status “OPEN”.

  • 25 random records which have status “CLOSED”.

  • 45 random records which have status “PENDING”.



Any help would be highly appreciated.

Answer

Use Union to combine multiple queries and remember to assign alias for each sub/parent query.

select * from ((Select * from Employee a where a.Status = 'OPEN' ORDER BY RAND() LIMIT 50) 
union 
(Select * from Employee b where b.Status = 'CLOSED' ORDER BY RAND() LIMIT 25) 
union 
(Select * from Employee c where c.Status = 'PENDING' ORDER BY RAND() LIMIT 45)) d;

I hope this will be useful for someone.