Selecting random rows from a table automatically

Question

I'm working on a project that requires back-end service. I am using MySQL and php scripts to achieve communication with server side. I would like to add a new feature on the back-end and that is the ability to generate automatically a table with 3 'lucky' members from a table_members every day. In other words, I would like MySQL to pick 3 random rows from a table and add these rows to another table (if is possible). I understand that, I can achieve this if manually call RAND() function on that table but ... will be painful!

There is any way to achieve the above?

UPDATE:

Here is my solution on this after comments/suggestions from other users

CREATE EVENT `draw` ON SCHEDULE EVERY 1 DAY STARTS '2013-02-13 10:00:00' ON COMPLETION NOT PRESERVE ENABLE DO 
INSERT INTO tbl_lucky(`field_1`) 
SELECT u_name 
FROM tbl_members 
ORDER BY RAND() 
LIMIT 3

I hope this is helpful and to others.

My advice is to create three random keys and then use an SQL `IN` clause to get the data from these three keys. — Preet Sangha, Feb 12 '13 at 22:49
use MySQL event scheduler and a procedure which randomly selects 3... each day and does the insert... — xQbert, Feb 12 '13 at 22:49
Thanks all of you for quick reply. @xQbert is possible to have an example code on this scenario? I'm new on this and do not know many stuff. — OutOfBoundsException, Feb 12 '13 at 22:52
@MiroslavHudak I would like the system automatically doing this every day or so. In addition, will work better if achieve to store this 'lucky' rows on another table. — OutOfBoundsException, Feb 12 '13 at 22:54
No idea what you mean by painful, but perhaps this will help you out. http://stackoverflow.com/questions/211329/quick-selection-of-a-random-row-from-a-large-table-in-mysql — Tony Hopkinson, Feb 12 '13 at 22:55

score 1 · Accepted Answer · answered Feb 12 '13 at 23:24

1

You can use the INSERT ... SELECT and select 3 rows ORDER BY RAND() with LIMIT 3

For more information about the INSERT ... SELECT statement - see

It's also possible to automate this every day job with MySQL Events(available since 5.1.6)

answered Feb 12 '13 at 23:24

Philipp

15,377
4
35
52

Is working fine! Thanks for your help. Just a small problem. I am trying to have TRUNCATE TABLE ... before INSERT INTO but for a reason I am getting syntax error on line 1. I've tried manually through phpMyAdmin and is working (asking for confirmation first). Any idea how to solve this in event? – OutOfBoundsException Feb 13 '13 at 00:10

Selecting random rows from a table automatically

1 Answers1