Lloyd Thomas wrote:
Revisted your query. I was being a bit lazy. changed 'select minute, max(events)' to 'select minute, events' and added 'order by minute' to give me what I needed.
Thanks
Lloyd,
If you want the whole table simply remove the outer select and add the order by clause as below.
select minutes.i as minute, count(*) as events
from
(select (event_time / 60) % 60 as begin_minute, ((event_time + duration) / 60) % 60 as end_minute
from event_data)
outer join integers60 as minutes
where
case
when begin_minute <= end_minute then
begin_minute <= minutes.i and minutes.i <= end_minute
else
begin_minute <= minutes.i or minutes.i <= end_minute
end
group by minute
order by minute
I though you wanted the minute with the most events. I added the outer select to show that that step can be done easily in the same query, rather than relying on PHP to extract this info from the full results for the hour.
Dennis Cote
