Another SQL question

[mvallevand]

Very elegant Martin unfortunately, there seems to be no guarantee that the EPG_EVENT oid is in the same sequence as start_time (at least not on my system)

Alex

Thanks, I was going to mention that the XMLTV source had to actually be in date order for that to work and for OTA EPG it might not but I didn't have any source to confirm.

Martin

[mvallevand]

Maybe this

Code:

select * from epg_event,
(select min(start_time) as start_time,channel_oid
from EPG_EVENT
where start_time > datetime('now')
group by channel_oid) next
where epg_event.start_time = next.start_time
and epg_event.channel_oid = next.channel_oid

Martin

[odin]

Maybe this

Code:

select * from epg_event,
(select min(start_time) as start_time,channel_oid
from EPG_EVENT
where start_time > datetime('now')
group by channel_oid) next
where epg_event.start_time = next.start_time
and epg_event.channel_oid = next.channel_oid

Martin

that only shows the 'next' on my system.

[bgowland]

Maybe this

Martin, I think you might have missed my post at #10 of this thread. It took a while to type as I was doing it between helping my wife with some chores and I think you missed it as you responded to Alex's post.

Cheers,
Brian

[mvallevand]

that only shows the 'next' on my system.

Wasn't that what bgowland asked for?

Martin

[bgowland]

Just to briefly revisit this (and for future reference).

I changed my requirements for the Now/Next query. The previous query returns two results for each channel (one for Now and the other Next...obviously).

I now need a single result for each channel which combines both. Took some time and gave me a bit of a headache. But I thought I'd share.

Code:

SELECT A.channel_oid AS _id, A.oid AS now_oid, A.title AS now_title, A.start_time AS now_start_time,
       A.end_time AS now_end_time, B.oid AS next_oid, B.title AS next_title, B.start_time AS next_start_time,
       B.end_time AS next_end_time
FROM (
    SELECT channel_oid,
        oid,
        title,
        start_time,
        end_time
    FROM epg_event
    WHERE  start_time <= datetime('now') AND end_time > datetime('now')
    ORDER BY channel_oid) AS A  
JOIN (
    SELECT channel_oid,
        oid,
        title,
        MIN(start_time) as start_time,
        end_time
    FROM epg_event
    WHERE start_time > datetime('now')  
    GROUP BY channel_oid)  AS B
ON A.channel_oid = B.channel_oid