I’m trying to update all the rows in my Devices table from another table called SysVersions, which has multiple rows matching each deviceID. Each row represents a new version of that device’s software and the timestamp of when it was installed.
I want to update the SysVersion field each Device record in the Devices table with the SysVersionValue at the highest timestamp from the set of matching records in the SysVersions table.
UPDATE Devices SET SysVersion = (SELECT SysVersionValue FROM SysVersions WHERE Devices.DeviceID = SysVersions.DeviceID AND SysVersions.SysVersionName = 'sys-version' AND Max (SysVersions.BeginTimestamp))
I think this SQL comes close but I get the error message: Error 1 misuse of aggregate function Max()
How do I select the matching record with the highest timestamp?
You need yet another subquery.
UPDATE Devices SET SysVersion = (SELECT SysVersionValue FROM SysVersions WHERE Devices.DeviceID = SysVersions.DeviceID AND SysVersions.SysVersionName = 'sys-version' AND SysVersions.BeginTimeStamp = (Select Max(SysVersions.BeginTimeStamp) where Devices.DeviceID = SysVersions.DeviceID and SysVersions.SysVersionName = 'sys-version)))
Great to hear from you again and thanks for the quick info. That makes sense but unfortunately it still fails with the same error message.
[quote]Error 1 misuse of aggregate: Max()
BeginTimestamp is a string in the SQLdate format… I assume the Max can deal with that?
Or do I need to sort the subquery descending and then somehow get the first row?
This is the last thing I need to sort out before today’s build so any other ideas are appreciated.
using max(date(BeginTimestamp)) might help so it compares these as dates
Duh. Select max (sysVersions.DeviceID) from SysVersions …
I never would have guessed that one, but I’ll give it a try…
Any chance you actually mean…
Select max (SysVersions.BeginTimeStamp) from SysVersions
Yes. Sorry for the mis-copy.
A typical SQL way of selecting the latest (or greatest) of something is to add this to the end of the query:
… order by myTimeStamp desc limit 1
Cool, I understand that one much better…
I’ll give it a try.
Ah… that’s a clever and interesting approach, although I wonder about the cost of a Sort (multiple passes) vs Max (single pass) In my case there are just a handful of sysversion records for each device so a sort should be cheap…
It’s worth considering the cost of the sort, certainly.
I suppose I have only used it when the field in question is indexed. Then the cost of the sort is fairly minimal.
Also, “Select Max(xxx)” certainly involves an implicit sorting function of some type - or something much cruder. In fact it might be a lot more inefficient. It probably depends on the internal implementation and optimization of the DBMS itself. I have a lot more experience with MySQL than SQLite but admittedly, I don’t know this level of detail about either of them.