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?
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;
Use:
SELECT d1.date, (
SELECT d2.title
FROM table_1 d2
WHERE d2.date = d1.date
ORDER BY RAND()
LIMIT 1
) AS title
FROM table_1 d1
GROUP BY d1.date
HAVING COUNT(d1.date)>=1
ORDER BY d1.date ASC
The only thing you should need to change is the name of your table. Make sure you only table table_1 to the actual name of your table, in both FROM clauses.
The idea is you retrieve the date, then you do a subquery to retrieve a random event within that date, by selecting from the same table, and joining on the date.
One way to do it
SELECT e.*
FROM
(
SELECT
(
SELECT id
FROM events
WHERE date = d.date
ORDER BY RAND()
LIMIT 1
) id
FROM
(
SELECT DISTINCT date
FROM events
) d
) q JOIN events e
ON q.id = e.id
ORDER BY date
Sample output:
| ID | TITLE | DATE | |----|--------------|------------------------------| | 2 | my event OO2 | April, 02 2014 00:00:00+0000 | | 4 | my event OO4 | May, 02 2014 00:00:00+0000 | | 8 | my event OO8 | June, 02 2014 00:00:00+0000 |
Here is SQLFiddle demo