CODEWITHSUNDEEP
sqlrandomsql-order-by
bklups
bklups

Reputation: 300

Display random results ordered by date

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?

Upvotes: 1

Views: 136

Answers (3)

Phil Cross
Phil Cross

Reputation: 9302

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.

Upvotes: 4

peterm
peterm

Reputation: 92795

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

Upvotes: 2

Vivek Jain
Vivek Jain

Reputation: 3889

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;

Upvotes: 3

Related Questions