YES Gary - many many thanks!! It produces the results required, but sadly takes a long time to execute (22 seconds) on my rpi - possibly because the readings table is quite large (14000 records) and the where clause is of course never satisfied. Any ideas for making it more efficient? At least I've solved my immediate problem though!! Many thanks indeed.
On Tuesday, 8 November 2016 23:48:28 UTC+2, gjr80 wrote: > Andrew, > > First off delete the single quotes, my mistake. I didn't actually use > 'table_name'.a or 'table_name'.b it was 'table_name' a and 'table_name' b > (note spaces not periods). This just creates aliases for table_name when we > join it to itself. So if the table name is readings the statement would > become: > > SELECT a.* > FROM readings a > LEFT JOIN readings b > ON a.id=b.id AND a.datetime<b.datetime > WHERE b.datetime IS NULL > ORDER BY id; > > Added the ORDER BY if you want it sorted on id. > > Gary > > On Tuesday, 8 November 2016 23:41:26 UTC+10, Andrew Milner wrote: >> >> Gary >> If the data is in a table called readings then I assume >> 'table_name'.a refers to readings - but what does 'table_name'.b referring >> to?? >> >> >> >> On Tuesday, 8 November 2016 10:51:16 UTC+2, gjr80 wrote: >> >>> Andrew, >>> >>> My SQL is somewhat rusty but what about something like: >>> >>> SELECT a.* >>> FROM 'table_name' a >>> LEFT JOIN 'table_name' b >>> ON a.id=b.id AND a.datetime<b.datetime >>> WHERE b.datetime IS NULL >>> >>> where the table is table_name. Not tested. >>> >>> Gary >>> >>> >>> -- You received this message because you are subscribed to the Google Groups "weewx-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
