Getting three random dates from each hour

The title doesn't actually fully describes the problem, that is: I have a table w开发者_StackOverflow社区ith dates -

 1. 2011-07-01 13:01:48
 2. 2011-07-01 13:09:36 
 3. 2011-07-01 13:21:24
 4. 2011-07-01 13:35:12
 5. 2011-07-01 13:49:23
 6. 2011-07-01 13:57:47
 7. 2011-07-01 14:05:12
 8. 2011-07-01 14:12:45
 9. 2011-07-01 14:31:48
 10. 2011-07-01 14:47:31

and so on. What I need is to get three random dates of each hour, for example:

 1. 2011-07-01 13:01:48
 2. 2011-07-01 13:21:24
 3. 2011-07-01 13:49:23
 4. 2011-07-01 14:05:12
 5. 2011-07-01 14:12:45
 6. 2011-07-01 14:47:31

How can I do it in mysql?

---

create table dates (`date` datetime);
insert into dates (`date`) values
('2011-07-11 06:05:02'),
('2011-07-11 06:15:02'),
('2011-07-11 06:45:02'),
('2011-07-11 06:55:02'),
('2011-07-11 06:56:02'),
('2011-07-11 08:05:02'),
('2011-07-11 08:07:02'),
('2011-07-11 08:09:02'),
('2011-07-11 08:11:02'),
('2011-07-11 08:40:02'),
('2011-07-11 09:05:02'),
('2011-07-11 11:10:02'),
('2011-07-11 11:11:02'),
('2011-07-11 11:55:02')
;

set @i := 0;
set @d := '';
select `date`
from (
    select  
        case 
            when @d != date_format(`date`, '%Y-%m-%d %H') 
            then @i := 0
            else @i := @i + 1
        end as i,
        case
            when @d != date_format(`date`, '%Y-%m-%d %H') 
            then @d := date_format(`date`, '%Y-%m-%d %H')
        end as d,
        case when @i < 3 then `date` else null end as `date`
    from (
        select `date`
        from dates
        order by date_format(`date`, '%Y-%m-%d %H'), rand()
    ) ss
) sw
where `date` is not null
order by `date`
;

---

This should work quite well if you have statistically enough rows per hour:

select *
from (
    select *
    from yourtable
    order by rand()
)
group by date(yourdate), hour(yourdate), floor(rand()*3)

---

if data set is not large -- if your data set is large then you should not use some random algorithm on the data set anyway, then you can use the following simple query:

select * from t order by rand() limit 6;

---

One thing you can do is write the query to select everything that you need, then store the results in either an array or a temporary table. If you use a temporary table, add a column for index, and when you insert your data into the temporary table, write the value into the index column as well. Then out of that array or temporary table, query for the records associated with what ever index is generated by the random number generator.