Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

"Select max version" query?

 
Janus Engstrøm
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What ORM is this question about? Hibernate?
 
Janus Engstrøm
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, forgot!

Yes, this question is related to Hibernate.
 
Janus Engstrøm
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
*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:



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
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic