aspose file tools*
The moose likes Object Relational Mapping and the fly likes Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark ""Select max version" query?" Watch ""Select max version" query?" New topic
Author

"Select max version" query?

Janus Engstrøm
Ranch Hand

Joined: Nov 01, 2006
Posts: 44
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

Joined: Apr 14, 2004
Posts: 10336

What ORM is this question about? Hibernate?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Janus Engstrøm
Ranch Hand

Joined: Nov 01, 2006
Posts: 44
Oh, forgot!

Yes, this question is related to Hibernate.
Janus Engstrøm
Ranch Hand

Joined: Nov 01, 2006
Posts: 44
*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

Joined: Feb 05, 2001
Posts: 17260
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Janus Engstrøm
Ranch Hand

Joined: Nov 01, 2006
Posts: 44
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: "Select max version" query?