Expanding results from a "max" query


#1

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?


#2

Hi,

what version are you using?
Have you tried:

SELECT expand(rid) FROM (SELECT @rid as rid, max(ts) AS ts FROM table where category='xyz' GROUP BY id, <another-property>)?

Thanks

Regards,
Michela


#3

Currently, we’re using version 2.2.29. We’re planning to move to 3.x early next year.


#4

Outside of whether one or more fields can be included in the GROUP BY clause (the answer appears to be “no” for version 2.2.29, and “maybe” for version 3.0.x), is this the most efficient way to do this sort of query, or is there a better alternative?