Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code 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
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

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: 4576
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: 4576
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.

 
Beauty is in the eye of the tiny ad.
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic