I have a table that contains records with a unique id and a timestamp. There are many ids, and each has many timestamps. I need to find the records that have their respective maximum value (one record each). The following query does that:
SELECT @rid, max(ts) AS ts FROM table WHERE category='xyz' GROUP BY id
This returns the @rid and the timestamp that is the maximum (i.e. “last”) record of each id. In order to make the query work, I need (I think) to include max(ts) in the projection, but by having it there, I can use expand() to return the actual underlying records, rather than just the @rid and the max timestamp.
So I came up with this:
SELECT expand(rid) FROM (SELECT @rid as rid, max(ts) AS ts FROM table where category='xyz' GROUP BY id)
It works, but I’m wondering if there is a different/better approach to this kind of query.
Also, this method only allows a single field to be included in the ‘GROUP BY’. On a traditional SQL server, this would be accomplished using a JOIN. What options are available in ODB to do this?