Hi ,

I am stuck in this problem. I have a table in which there is a field called "drvloadcount" which is an integer. I need to retrieve, given two timestamps the min and max drvloadcount within that range and also the (min-1) drvloadcount and max+1 drvloadcount(which obviously would fall beyond the timestamps). I have formed the following sql query for the same


select t1.origdrvsn,t1.drvloadcount,t1.timestamp,t2.drvloadcount as prevloadcount, t2.timestamp as prevtimestamp,
t3.timestamp as "max timestamp", t3.drvloadcount as "max drive loadcount",
t4.timestamp as "post timestamp", t4.drvloadcount as "Post load count" from tickets t1
inner join tickets t2 on t2.origdrvsn= tr.origdrvsn and t2.drvloadcount = t1.drvloadcount-1
inner join tickets t3 on t3.timestamp in (select max(timestamp) from tickets
where origdrvsn='HU10048JKT'
and timestamp>1316857928 and timestamp<1317203532)
inner join tickets t4 on t1.origdrvsn = t4.origdrvsn and t4.drvloadcount=t3.drvloadcount+1
where t1.timestamp in (select min(timestamp) from tickets
where origdrvsn='HU10048JKT'
and timestamp>1316857928 and timestamp<1317203532)




(origdrvsn and drvloadcount together form the primary key for this table.) Is there a way I can write the query using Criteria API.

Thanks
Nimesh