"Select max version" query?

[Janus Engstrøm]

Hi!

I have persisted some objects with different version numbers. Now I would like to make a query that returns the objects with the latest version number. How do I do that?

E.g.

Obj 1 version 1
Obj 1 version 2
Obj 1 version 3
Obj 2 version 1
Obj 2 version 2
Obj 3 version 1

Now I would like to fetch Obj 1 version 3, Obj 2 version 2, and Obj 3 version 1 from the database.

I hope you guys can help me out here.

[Paul Sturrock]

What ORM is this question about? Hibernate?

[Janus Engstrøm]

Oh, forgot!

Yes, this question is related to Hibernate.

[Janus Engstrøm]

*bump* (hope it's alright to bump?)

Hibernate-related question.

Perhaps it would help if I tried to explain what I'm looking for in another way:

For each of the various names in a table, return the ones with the highest number:

TABLE
 
 id  name   number
 1   ABC      1
 2   ABC      2
 3   ABC      3
 4   XYZ      1
 5   JKL      1
 6   JKL      2

The query I'm looking for should return the objects 3, 4 and 6, as object 3 has the highest number of all the ABC-names, object 4 has the higest number for all the XYZ-names, etc.

Hope you can help me out here


Thanks,

Janus
[ August 21, 2008: Message edited by: Janus Engstr�m ]

[Mark Spritzler]

My first suggestion is how do you do that in SQL, and then convert it to HQL.

Or if you didn't want to think, I would look at using MAX in your query Maybe GroupBy and Having

in HQL

I would first try

"select o.name, MAX(o.number) From MyObject o Groupby o.name"

Now it will return an Object[][], but it will give you the results you are looking for, Or you might have to use the query above as a sub-query in "From MyObject o Where o.number = (<<SUBSELECT HERE)"

Mark

[Janus Engstrøm]

Excellent!

It all boils down to my ignorance concerning SQL, your example does the trick.

It's almost a perfect solution, there's just a single nag about it: Subqueries can only return a single column, so I cannot use yor SELECT ... GROUP BY-query as a subquery. As I see it, the SELECT ... GROUP BY-query must involve columns name and number to produce the desired result or am I overlooking something?

Oh, and another thing: Say your Subquery idea worked. Given the dataset in my previous post, it would return a list containing the values [3, 1, 2] and hence the objects with id 3, 4, 6 would be returned, right?
What if the 6th row of the dataset was deleted, then the subquery would return a list containing the values [3, 1, 1], right? If this is correct, how does the outer SELECT know which objects to return, as the values aren't unique?
Just a thought...


But thank you very much for your input, it most certainly solved my problem!





Regards,

Janus