ansaurus

Question

Answer 1

+1 A:

You can use a WHERE clause...

WHERE date = 'insert date' ORDER BY rand() LIMIT 1`

Then just use PHP to insert whatever the date is into the string there for each date.

Or to use only one MySQL query, simply order them by date descending and pull them all out using PHP...

while ($item = mysql_fetch_array($query)):
    // process to figure out the date (I'm sure you can do that)
    $mydates[$date][] = $id; // add that ID to that date array
endwhile;
foreach ($mydates as $date):
    $key = array_rand($date);
endforeach;

animuson 2010-07-06 18:08:16

That helps, but i would need to do a query per day

Rodrigo 2010-07-06 18:23:52

Not if you do the second option. The second one you just order them by date and then do all the processing in PHP, which would actually be much faster.

animuson 2010-07-06 18:48:01

Answer 2

A:

Why not use php to select a random number and use the id as an index.

$randid = rand(1, 9);
$result = mysql_query("SELECT FROM 'dates' WHERE id = '".$randid."' ORDER BY 'date'");

Using rand() in mysql is bad because the values are copied to a temporary table which makes everything slow.

Neb 2010-07-06 18:10:21

That's not what he wants. He wants to group each date and select a random one from the group, but GROUP BY won't work.

animuson 2010-07-06 18:12:39

But then all he really wants is to get mysql to order the table. First of all I think he should use timestamps not dates. The random part has nothing exactly to do with mysql(except of the rand function, which makes things slow, source:http://akinas.com/pages/en/blog/mysql_random_row/)

Neb 2010-07-06 18:15:10

If all that is required is a single row, then this will work. However, this selects one row from the table by ID, so the `ORDER BY` is not needed. If multiple rows are required, as appears to be the case (I admit I'm not 100% clear on that), then this query would have to be run multiple times to get back a full data set, and it would then require more work to remove duplicates.

Mike 2010-07-06 19:08:07

Answer 3

+2 A:

Probably not very efficient, but try

select * from (select * from tbl order by rand()) as t group by date(md)

KennyTM 2010-07-06 18:38:58

thanks didn't noticed before :P

Rodrigo 2010-07-06 19:15:10

Answer 4

+1 A:

If I have understood your question correctly, this should do the trick:

SELECT * FROM table1 ORDER BY DATE(md) DESC, RAND();

Example:

DROP TABLE IF EXISTS `table1`;

CREATE TABLE `table1` (
    `id` int(11) NOT NULL auto_increment,
    `md` DATETIME,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

DELIMITER //
DROP PROCEDURE IF EXISTS autofill//
CREATE PROCEDURE autofill()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 50 DO
        INSERT INTO table1 (md) VALUES (DATE_ADD(NOW(), INTERVAL FLOOR(RAND() * 1000) HOUR));
        SET i = i + 1;
    END WHILE;
END;
//
DELIMITER ;

CALL autofill();

SELECT * FROM table1 ORDER BY DATE(md) DESC, RAND();

+----+---------------------+
| id | md                  |
+----+---------------------+
|  8 | 2010-08-16 13:46:04 |
| 16 | 2010-08-15 19:46:05 |
| 47 | 2010-08-15 18:46:06 |
| 25 | 2010-08-15 15:46:05 |
| 33 | 2010-08-15 09:46:05 |
|  3 | 2010-08-14 20:46:04 |
| 45 | 2010-08-13 03:46:06 |
| 17 | 2010-08-12 13:46:05 |
| 12 | 2010-08-12 06:46:05 |
|  7 | 2010-08-12 15:46:04 |
| 37 | 2010-08-12 14:46:05 |
| 40 | 2010-08-10 05:46:06 |
| 13 | 2010-08-09 10:46:05 |
|  4 | 2010-08-09 00:46:04 |
|  1 | 2010-08-06 12:46:04 |
| 28 | 2010-08-02 23:46:05 |
| 15 | 2010-08-02 10:46:05 |
|  2 | 2010-08-02 01:46:04 |
| 38 | 2010-07-31 13:46:06 |
| 27 | 2010-07-31 04:46:05 |
| 30 | 2010-07-31 03:46:05 |
| 22 | 2010-07-31 08:46:05 |
| 50 | 2010-07-30 05:46:06 |
| 11 | 2010-07-28 13:46:05 |
| 18 | 2010-07-28 21:46:05 |
| 29 | 2010-07-27 23:46:05 |
| 35 | 2010-07-27 11:46:05 |
|  6 | 2010-07-26 20:46:04 |
| 20 | 2010-07-25 03:46:05 |
| 31 | 2010-07-23 07:46:05 |
| 14 | 2010-07-23 23:46:05 |
| 23 | 2010-07-23 10:46:05 |
| 48 | 2010-07-23 17:46:06 |
| 42 | 2010-07-21 03:46:06 |
| 39 | 2010-07-20 05:46:06 |
| 36 | 2010-07-18 05:46:05 |
| 10 | 2010-07-17 01:46:05 |
| 32 | 2010-07-16 06:46:05 |
|  9 | 2010-07-16 15:46:04 |
| 24 | 2010-07-16 10:46:05 |
| 43 | 2010-07-16 09:46:06 |
|  5 | 2010-07-14 01:46:04 |
| 21 | 2010-07-14 08:46:05 |
| 49 | 2010-07-13 07:46:06 |
| 41 | 2010-07-13 15:46:06 |
| 46 | 2010-07-12 04:46:06 |
| 44 | 2010-07-11 16:46:06 |
| 26 | 2010-07-10 14:46:05 |
| 34 | 2010-07-09 16:46:05 |
| 19 | 2010-07-07 01:46:05 |
+----+---------------------+

Mike 2010-07-06 18:47:30

Thanks mike, i've tried the same here, but there's a issue, the rand() function argument is a just seed number, don't affect the date(md) sorting

Rodrigo 2010-07-06 19:10:31

The 2nd query (the GROUP BY one) doesn't seem to work.

KennyTM 2010-07-06 19:12:18

this gave me an idea that solved the problem select * from (SELECT * FROM table1 ORDER BY rand()) as x order by day(md);

Rodrigo 2010-07-06 19:14:32

@Rodrigo: That's true - just remove `TIME(md)` from `RAND(TIME(md))` to leave `RAND()`, and it works. I have run it repeatedly, and get the results in a different order each time. @KennyTM: Oh yes - it's the same result each time. :-( Let's hope that the non-grouped result is what Rodrigo is after :-)

Mike 2010-07-06 19:20:43

I've edited the answer to reflect the comments.

Mike 2010-07-06 19:22:31

ansaurus

tags:

views:

answers:

MySQL order by rand() grouped by day

related questions