显示按日期排序的随机结果

Question

This is my database table structure:

-----------------------------------
|  id |     title    |    date    |
-----------------------------------
| 001 | my event OO1 | 04/02/2014 |
| 002 | my event OO2 | 04/02/2014 |
| 003 | my event OO3 | 04/02/2014 |
| 004 | my event OO4 | 05/02/2014 |
| 005 | my event OO5 | 05/02/2014 |
| 006 | my event OO6 | 05/02/2014 |
| 007 | my event OO7 | 05/02/2014 |
| 008 | my event OO8 | 06/02/2014 |
| 009 | my event OO9 | 06/02/2014 |
-----------------------------------

I succceed to display it like it :

04/02/2014
- my event OO1
- my event OO2
- my event OO3
05/02/2014
- my event OO4
- my event OO5
- my event OO6
- my event OO7
06/02/2014
- my event OO8
- my event OO9

But I want to display just 1 result (event) per date randomly like:

04/02/2014
- my event OO2
05/02/2014
- my event OO6
06/02/2014
- my event OO8

On my sql result, I have tried ORDER BY RAND() LIMIT 1 but of course I have not sorted by date so results are randomly ordered

So, how to get date sorted and have random results?

Answer 1

SELECT distinct t1.date, 
    (SELECT t2.ID, t2.title 
    FROM mytable as t2 
    WHERE t2.date = t1.date 
    ORDER BY RAND()
    LIMIT 1) AS event_title
FROM mytable as t1 
ORDER BY t1.date;