Hibernate Query - Get latest versions by timestamp?

Question

I have a database that is being used as a sort of version control system. That is, instead of ever updating any rows, I add a new row with the same information. Each row also contains a version column that is a date timestamp, so the only difference is the new row will have a more recent timestamp.

What I'm having trouble with is writing an efficient hibernate query to return the latest version of these rows. For the sake of example, these are rows in a table called Product, the timestamped column is version. There are multiple versions of multiple products in the table. So there may be multiple versions (rows) of ProductA, multiple versions of ProductB, etc. And I would like to grab the latest version of each.

Can I do this in just a single hibernate query?

session.createQuery("select product from Product product where...?");

Or would this require some intermediate steps?

Answer 1

To find the latest version of a particular product:

select product where ... order by version DESC

put a compound key with the version too.

To find the latest version of all products, you need two queries (or a subquery):

Insert into TMP (id, version) select (id, max(version)) from product group by id);
select P.* Product P, TMP where TMP.ID = P.ID

Answer 2

To answer this, each product needs some kind of identifier. Since there can be multiple versions, we need to know "what is a product" I'm assuming product.id is out, as this is probably a surrogate key. I'll choose product.name for sake of example.

Here's a single query to retrieve the latest version of each product:

select p1 from Product p1 where
  p1.timestamp >= all (
     select p2.timestamp from Product p2 where
      p2.name=p1.name
  )

(My HQL is a bit rusty, but I hope you get the gist.)

The trick is the self join between like products of differing timestamps. p1 products are more recently modified than p2. To find the most recent p1, we find rows where there are no p2 values - i.e. there are no product more recent than p1.

EDIT: I've revised the query - I'd saw someone used the "on" syntax in a forum post recently, but I now remember that that is not valid HQL. Sorry about that - I don't have a system available to test on. It now uses a correlated subquery that functions the same as the JOIN.

Hope this helps.