Selecting the matching record with the highest timestamp?

Databases
1

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?

Thanks, Joe

2

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)))

3

HI Tim

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()
[/quote]

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.

Thanks, Joe

4

using max(date(BeginTimestamp)) might help so it compares these as dates

5

Duh. Select max (sysVersions.DeviceID) from SysVersions …

6

Tim

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
7

Yes. Sorry for the mis-copy.

8

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

9

Tim

Cool, I understand that one much better… :slight_smile:

I’ll give it a try.

Thanks, Joe

10

Steve

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…

Thanks, Joe

11

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.