Hibernate version: 2.1
Mapping documents: N/A
Code between sessionFactory.openSession() and session.close(): N/A
Full stack trace of any exception that occurs: N/A
Name and version of the database you are using: Sql Server 2000
The generated SQL (show_sql=true): N/A
Debug level Hibernate log excerpt: N/A

I have a table that has a timestamp column. What is the most efficient way to retrieve the most recent record? There could be many records in this table and I am afraid ORDER BY and subselects might get too slow, but maybe that is not a valid concern.

I want to do something like "Select emp.lastName from Employee emp where emp.captureTime is most recent".

Any suggestions for the most efficient way to do this?

Thanks for any help.

I believe you need a subselect. I would use the native SQL API so you have control over the SQL and dont have to fumble with HQL, but maybe that is just me:

http://www.hibernate.org/hib_docs/refer ... -nativesql

Something like

select {emp.*} from Employee emp where emp.captureTime = (select max(emp2.captureTime) from Employee emp2)

If you have a large table, you might also want to try this and see which is better (I would think the former is better or equal the latter):

select {emp.*} from Employee emp where not exists (select 1 from Employee employee2 where employee2.captureTime > employee.captureType)

However, Im not 100% sure this is the most efficient, anyone feel free to correct me.

Regards,
Chris