Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Another SQL question

  1. #1
    Join Date
    Dec 2004
    Location
    West Yorkshire, UK
    Posts
    4,497

    Another SQL question

    I'm still not sure why I struggle with SQL of all things. It must be a blind-spot of mine.

    Anyway, to the point, I need to find the 'Next' programme to go along with a NOW/NEXT type of view. Doing NOW is easy and I thought NEXT would be too.

    So what's wrong with this SQL query which basically tries to find 1 programme for each channel which hasn't started yet...
    Code:
    select * from epg_event where start_time >= datetime('now') GROUP BY channel_oid
    ...it works and does only provide one programme for each channel but the start_time columns are showing me dates of either the 6th or 7th of July. It seems it's showing me the very last programme for each channel in my EPG_EVENT table whereas I obviously need the very first for each channel which hasn't started yet.

    Cheers,
    Brian

  2. #2
    Join Date
    Jun 2007
    Location
    Scotland
    Posts
    2,411
    The basic query will return every programme which has not yet started - the GROUP BY then rolls them up by channel_oid so you get a single programme for each group - you would seem to be getting the last when you really want the first.

    My knowledge of sql syntax is very elementary, but I would expect there to be some modifer which would allow you to specify First - unfortunately, I haven't a clue what it is - sorry

    Alex

  3. #3
    Join Date
    Dec 2004
    Location
    West Yorkshire, UK
    Posts
    4,497
    Yep, that's pretty much it.

    I've been hopping between the SQLite docs and SQLiteSpy testing various queries and I still can't get my head around it. I even managed to return 240,000+ results with one query when there's only ~9500 events in the table and I only want the next 25 events for the channels I have.

    Another tweak and I think SQLiteSpy went in to recursive meltdown and I had to kill it with Task Manager.

    Brain hurts - time to grab a beer and stare at the TV I think.

    Cheers,
    Brian

  4. #4
    Join Date
    Jan 2005
    Location
    London
    Posts
    2,859
    how about

    SELECT TOP 1 * from epg_event where start_time >= datetime('now') ORDER BY thedatetime GROUP BY channel_oid
    Silverstone LC17 Case,
    ASUS P5Q, Intel E7200, 4GB RAM
    Blackgold DVB-T2, ATI 7750, Windows 7 64-bit
    SAF codec pack,Sony 40" Z5500

  5. #5
    Join Date
    Dec 2008
    Location
    DFW, USA
    Posts
    81
    Does sqllite support 'SELECT TOP 1 *...' syntax?

    Is there a table for channel oids? I'll call it 'channel_oid_table' (I don't have the scheme up in front of me). If there a table, then something like this might get close:


    SELECT * from epg_event where start_time > datetime('now') ORDER BY thedatetime GROUP BY channel_oid LIMIT (select COUNT(*) FROM channel_oid_table)


    There is still the problem of where a channel might have multiple programs started before one of the channels has a program start. This would be so much easier with a little C# or Perl to process the results set.
    EVGA 730i 113-YW-E115
    C2D E8400 C0 Stepping
    4gb GSkill F2-8000CL5D-4GBPQ
    500 GB Seagate SATAII
    Win7
    HVR-2250 TV Tuner
    NPVR 2.3.6

  6. #6
    Join Date
    Oct 2011
    Location
    UK
    Posts
    79
    Brian, here's my 'now and next' query that I've just written on my sqllite db. It is working for me. Give it a try and see if this is what you want or if you can make it more 'elegant'.. This uses a subquery which is a union..

    Code:
    SELECT
    b.oid,b.channel_oid,b.description,b.start_time,b.end_time
    FROM epg_event b,
    (SELECT
    MIN(a.start_time) as start_time,
    a.channel_oid
    FROM epg_event a
    WHERE 
    a.start_time > datetime('now','localtime')
    GROUP BY a.channel_oid
    UNION
    SELECT
    MIN(a.start_time) as start_time,
    a.channel_oid
    FROM epg_event a
    WHERE 
    a.end_time >= datetime('now','localtime')
    GROUP BY a.channel_oid
    ) c
    WHERE
    c.start_time = b.start_time and c.channel_oid = b.channel_oid
    ORDER by b.channel_oid, b.start_time
    Last edited by odin; 2012-06-24 at 11:58 AM.

  7. #7
    Join Date
    May 2006
    Location
    Canada
    Posts
    21,521
    I'd use something like

    Code:
    select * from EPG_EVENT
    where oid in 
    (select min(oid)
    from EPG_EVENT
    where start_time > datetime('now')
    group by channel_oid)
    Martin

  8. #8
    Join Date
    Jun 2007
    Location
    Scotland
    Posts
    2,411
    Quote Originally Posted by odin View Post
    Brian, here's my 'now and next' query that I've just written on my sqllite db. It is working for me. Give it a try and see if this is what you want or if you can make it more 'elegant'.. This uses a subquery which is a union..
    That worked for me after I made the changes underlined otherwise it didn't like the c.channel_oid in the final SELECT clause...
    Code:
    SELECT
    b.oid,b.channel_oid,b.description,b.start_time,b.end_time
    FROM epg_event b,
    (SELECT
    MIN(a.start_time) as start_time,
    a.channel_oid as channel_oid
    FROM epg_event a
    WHERE 
    a.start_time > datetime('now','localtime')
    GROUP BY a.channel_oid as channel_oid
    UNION
    SELECT
    MIN(a.start_time) as start_time,
    a.channel_oid
    FROM epg_event a
    WHERE 
    a.end_time >= datetime('now','localtime')
    GROUP BY a.channel_oid
    ) c
    WHERE
    c.start_time = b.start_time and c.channel_oid = b.channel_oid
    ORDER by b.channel_oid, b.start_time.
    Alex

  9. #9
    Join Date
    Jun 2007
    Location
    Scotland
    Posts
    2,411
    Quote Originally Posted by mvallevand View Post
    I'd use something like

    Code:
    select * from EPG_EVENT
    where oid in 
    (select min(oid)
    from EPG_EVENT
    where start_time > datetime('now')
    group by channel_oid)
    Martin
    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

  10. #10
    Join Date
    Dec 2004
    Location
    West Yorkshire, UK
    Posts
    4,497
    Thanks guys.

    gEd, as rookie suggests 'TOP' isn't supported by SQLite (I believe it's a Transact-SQL extension). Unfortunately, rookie, your query doesn't work - something about a SQLite rule that 'ORDER BY' must appear at the end of a query.

    Thanks odin and Martin - after I posted my original question, I realised MIN(...) was going to be the key to it but couldn't get it to work exactly.

    I'd be slightly concerned about using MIN(oid) though Martin. I'm sure it will work for guide data supplied by XMLTV sources where the XML is ordered by date/time (mine is). In the case of harvesting the EPG data from the broadcast stream, however, there's no guarantee in what order the events will be written to the database and oid is an autoincrement. It is possible, I think, that a section of a DVB EIT could be harvested and written before another section which contains earlier events. Not sure.

    Thanks odin for combining the NOW query in with NEXT - I already had a working NOW query but my next question would probably have been how to combine the two.

    I had to adjust it slightly - in particular, datetime('now', 'localtime') returns a datetime adjusted to the user's locale (as you'd expect) but all EPG_EVENT data is normalized to UTC datetimes so datetime('now') is what's needed. I also had to adjust the criteria for the datetime parts slightly to get correct results.

    Anyway - for any future readers of this thread, the following works exactly...
    Code:
    SELECT
    b.oid,b.channel_oid,b.title,b.start_time,b.end_time 
    FROM epg_event b,
    (SELECT
    MIN(a.start_time) as start_time,
    a.channel_oid, a.end_time 
    FROM epg_event a
    WHERE 
    a.start_time > datetime('now')  
    GROUP BY a.channel_oid
    UNION
    SELECT
    MIN(a.start_time) as start_time,
    a.channel_oid, a.end_time 
    FROM epg_event a
    WHERE 
    a.start_time <= datetime('now') AND a.end_time > datetime('now')
    GROUP BY a.channel_oid
    ) c
    WHERE
    c.start_time = b.start_time and c.channel_oid = b.channel_oid
    ORDER by b.channel_oid

Posting Permissions

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