Ankur Mahajan Ankur Mahajan - 1 year ago 67
SQL Question

MySql - Get random records based on the column value

I have a sql table,

which has 4 columns.

  • Id

  • Name

  • Address

  • Status – Fixed Type (Enum). Having 3 fixed values –

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 Source

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) 
(Select * from Employee b where b.Status = 'CLOSED' ORDER BY RAND() LIMIT 25) 
(Select * from Employee c where c.Status = 'PENDING' ORDER BY RAND() LIMIT 45)) d;

I hope this will be useful for someone.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download