MySQL order by rand () grouped by day

Question

Can I get random items on the same day?

For instance:

+ ---- + --------------------- +  
| id | md |  
+ ---- + --------------------- +  
| 1 | 2010-06-27 11:26:01 |  
| 2 | 2010-06-27 11:28:20 |  
| 3 | 2010-06-27 11:29:46 |  
| 4 | 2010-06-27 11:30:50 |  
| 5 | 2010-06-27 12:20:56 |  
| 6 | 2010-06-27 12:27:42 |  
| 7 | 2010-06-27 15:14:05 |  
| 8 | 2010-07-06 01:53:33 |  
| 9 | 2010-07-06 01:52:52 |  
+ ---- + --------------------- +  

I want to select random items on the same day, but at the same time I want it to be sorted by date in descending order. Something like that:

+ ---- + --------------------- +  
| id | md |  
+ ---- + --------------------- +  

| 8 | 2010-07-06 01:53:33 | random block  
| 9 | 2010-07-06 01:52:52 |  

| 2 | 2010-06-27 11:28:20 | random block  
| 4 | 2010-06-27 11:30:50 |  
| 1 | 2010-06-27 11:26:01 |  
| 6 | 2010-06-27 12:27:42 |  
| 3 | 2010-06-27 11:29:46 |  
| 5 | 2010-06-27 12:20:56 |  
| 7 | 2010-06-27 15:14:05 |  

+ ---- + --------------------- +  

I don’t know how to start, or if it’s possible, since order by rand () will not accept grouping.

Answer 1

, ,

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

Answer 2

:

SELECT * 
FROM tbl 
ORDER BY md DESC, RAND()

Answer 3

If I 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 |
+----+---------------------+

Answer 4

You can use the WHERE clause ...

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

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

Or use only one MySQL query, just order them by date in descending order and pull them all out with 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;