This week's giveaway is in the JDBC forum.
We're giving away four copies of Java Database Connections & Transactions (e-book only) and have Marco Behler on-line!
See this thread for details.
Win a copy of Java Database Connections & Transactions (e-book only) this week in the JDBC 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

HQL Query to return the single most recent record, but looking at two (not one) date columns  RSS feed

 
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I have a table for storing items, and wish to receive the single most recently created or updated record.  The table has a 'created' date column and an 'updated' date column.  Of course I could easily separately retrieve the record with the latest created date, and the record with the latest updated date, then compare the two in my code.  But, partly for performance reasons, I'd prefer to do this using the one HQL query, and have just the one record back.

I don't imagine this is such an unusual need, but a Google search revealed no online examples that fit the bill.

Regards,

Chris.
 
Christopher Dodunski
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
By the way, here is the HQL query I wrote, but it errors.  It's the first time I've ever attempted to use greatest() in an HQL named query.
 
Master Rancher
Posts: 4087
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
greatest is supposed to be used as part of an ORDER BY.

So (and this is hand written, so is likely to have errors):



So it doesn't transfer everything from the database, as you only want the newest, you need to use the setMasResults() method on Query to limit the number returned.
Set it to 1, and the result will have the newest Movement.
 
Christopher Dodunski
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:greatest is supposed to be used as part of an ORDER BY.

So (and this is hand written, so is likely to have errors):



So it doesn't transfer everything from the database, as you only want the newest, you need to use the setMasResults() method on Query to limit the number returned.
Set it to 1, and the result will have the newest Movement.



Ahh... thanks Dave!  I expected that there would be a simple explanation.  :-)
 
Dave Tolls
Master Rancher
Posts: 4087
47
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You'd found all the parts, they just weren't in quite the right place.
 
Christopher Dodunski
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:You'd found all the parts, they just weren't in quite the right place.



And just to add another chapter to the story, I had to incorporate the coalesce() function within the HQL to deal with the case of 'updated' potentially being null.  Without this, records with null in the 'updated' column got thrown to the end of the result set, despite having a recent 'created' date.  An unexpected behaviour of the greatest() function.

 
No matter. Try again. Fail again. Fail better. This time, do it with this tiny ad:
how do I do my own kindle-like thing - without amazon
https://coderanch.com/t/711421/engineering/kindle-amazon
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!