Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Another SQL question

  1. #11
    Join Date
    May 2006
    Location
    Canada
    Posts
    20,744
    Quote Originally Posted by ACTCMS View Post
    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

  2. #12
    Join Date
    May 2006
    Location
    Canada
    Posts
    20,744
    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

  3. #13
    Join Date
    Oct 2011
    Location
    UK
    Posts
    79
    Quote Originally Posted by mvallevand View Post
    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.

  4. #14
    Join Date
    Dec 2004
    Location
    West Yorkshire, UK
    Posts
    4,484
    Quote Originally Posted by mvallevand View Post
    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

  5. #15
    Join Date
    May 2006
    Location
    Canada
    Posts
    20,744
    Quote Originally Posted by odin View Post
    that only shows the 'next' on my system.
    Wasn't that what bgowland asked for?

    Martin

  6. #16
    Join Date
    Dec 2004
    Location
    West Yorkshire, UK
    Posts
    4,484
    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
    Last edited by bgowland; 2014-05-21 at 08:13 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •