| Re: [eclipselink-users] Help with having clause |
You can use a sub-select for this. Something like: (JPQL) Select object from MyObject object where object.externalIdentifier = :id and object.organization = :org and object.sourceRecordDate = (Select Max(inner.sourceRecordDate) from MyObject inner where inner.externalIdentifier = :id and inner.organization = :org and inner.sourceRecordDate <= :date) cmathrusse wrote: > > Well, if my situation was that simple I would use em.find(...) My > apologies for not explaining it better. > > ID discriminator SOURCE_RECORD_DATE ADDRESS_1 > EXTERNAL_IDENTIFIER ORGANIZATION > 19766 BillTo 2009-08-12 10:34:43.893 4567 Test > Dr. 31295-90 AMER > 19775 BillTo 2009-08-20 16:18:19.27 4567 Test > Dr. 31295-90 AMER > 19776 BillTo 2009-08-20 16:55:07.02 4567 Test > Dr. 31295-90 AMER > > > Looking at the data, the records are virtually identical all except for > the SOURCE_RECORD_DATE. So basically I'm attempting a SELECT based upon > the records EXTERNAL_IDENTIFIER and the ORGANIZATION, which will match all > 3 records. So given a date of 2009-08-20 16:30:00.00, I would like to > retrieve the record that has a SOURCE_RECORD_DATE that has the greatest > value that is equal to or less than the supplied date, that would be the > row that has id 19775. If my understanding is correct, the only way that I > am going to retrieve the single matching row is to use a HAVING clause to > further filter the matching rows. > > Thanks... > Chris > > ----- http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland http://www.eclipse.org/eclipselink/ EclipseLink , http://www.oracle.com/technology/products/ias/toplink/ TopLink Wiki: http://wiki.eclipse.org/EclipseLink EclipseLink , http://wiki.oracle.com/page/TopLink TopLink Forums: http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , http://www.nabble.com/EclipseLink-f26430.html EclipseLink Book: http://en.wikibooks.org/wiki/Java_Persistence Java Persistence -- View this message in context: http://www.nabble.com/Help-with-having-clause-tp25138774p25151372.html Sent from the EclipseLink - Users mailing list archive at Nabble.com.