Win a copy of Programmers Guide to Apache Thrift this week in the Open Source forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Knute Snortum
  • Paul Clapham
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Frits Walraven
Bartenders:
  • Ganesh Patekar
  • Tim Holloway
  • salvin francis

"Select max version" query?  RSS feed

 
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.
 
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 ]
 
ranger
Posts: 17344
11
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
 
Something about .... going for a swim. With this tiny ad ...
ScroogeXHTML - small and flexible RTF to HTML converter library
https://coderanch.com/t/710903/ScroogeXHTML-RTF-HTML-XHTML-converter
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!