This week's book giveaway is in the Java in General forum.
We're giving away four copies of Event Streams in Action and have Alexander Dean & Valentin Crettaz on-line!
See this thread for details.
Win a copy of Event Streams in Action this week in the Java in General 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
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Knute Snortum
  • Rob Spoor
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Piet Souris
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Tim Holloway
  • 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: 4188
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: 4188
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.

  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!