Another SQL question

[bgowland]

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. :confused:

Cheers,
Brian

[ACTCMS]

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

[bgowland]

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. :eek:

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

Cheers,
Brian

[gEd]

how about

SELECT TOP 1 * from epg_event where start_time >= datetime('now') ORDER BY thedatetime GROUP BY channel_oid

[rookie]

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:


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


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.

[odin]

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

[mvallevand]

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

[ACTCMS]

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 [U]as channel_oid[/U]
FROM epg_event a
WHERE
a.start_time > datetime('now','localtime')
GROUP BY a.channel_oid [U]as channel_oid[/U]
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

[ACTCMS]

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

[bgowland]

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