• 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
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Ganesh Patekar

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

 
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.
 
Rancher
Posts: 4271
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
Rancher
Posts: 4271
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.

 
You've gotta fight it! Don't give in! Read this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!