No David - I wanted the latest reading for each ID, and I do not know how many ID's there are. Your sql just gives the single latest reading in the table - not what I wanted but thanks for the input.
Gary's sql has enabled me to produce the required view - albeit requiring the use of an additional view due to the limitations on my version of MySQL. On Wednesday, 9 November 2016 16:14:44 UTC+2, David Hathaway wrote: > Based on the initial post, it seems you want all the columns for the > latest datetime? If so, I think y'all are working too hard: > > Try, > > SELECT * > FROM 'table_name' > ORDER BY datetime DESC > LIMIT 1 > > I put the various parts on separate lines for emphasis. I'd have it all > on one line, separated by spaces. > > - The *LIMIT 1* part says give me only one row > - The *ORDER BY datetime DESC* part says to sort by datetime, biggest > values first > - Together, these give the 1 row that has the highest datetime. > > > This should be a very efficient statement since it use core functions and > the last record is probably still in MySQL's memory. > > Dave > > > On Wednesday, November 9, 2016 at 12:59:17 AM UTC-6, Andrew Milner wrote: >> >> Thanks Gary - done my homework, created a subquery for today's readings >> (since MySQL can't have a subquery in a view) and selected from that. >> Still do not fully understand the where datetime is null part though - but >> it works (down to a second now) >> >> On Wednesday, 9 November 2016 07:32:46 UTC+2, gjr80 wrote: >>> >>> The WHERE clause must be satisfied or surely there would be no results >>> at all? >>> >>> As for optimising, that is left as homework... Seriously, you might find >>> some info by googling 'SQL optimise left join', quite a few results but a >>> bit heavy for me. Sorry but it was a stretch coming up with the query. >>> >>> 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
