Hi Guys,

I am new to hibernate, so please excuse if this is a very elementary doubt. I tried searching on the net about this, without much success.

In our application we are using the following criteria query.

Session m_HibernateSession = HibernateUtil.currentSession();
Criteria m_SelectionCriteria = m_HibernateSession.createCriteria (nativeVO.class);
m_SelectionCriteria.add(Expression.eq("_Mnemonic", p_LocationCode));
m_SelectionCriteria.add(Expression.sql("ROWNUM=1"));
m_SelectionCriteria.addOrder(Order.desc("_LastModifiedDate"));
m_Native = (nativeVO)m_SelectionCriteria.uniqueResult();

Note that the ROWNUM=1 criteria is added before the Order criteria.

- Does that mean the Order criteria is actually useless?(because we are limiting the number of rows to 1, before sorting it).
- Or is it that that the result is sorted first and then, the first row is returned.

Thanks a lot in advance.

in this case the orderCriteria is useless because uniqueResult() is supposed to deliver 1 single result set. And it expects only a single result set.

I think I might have realized what the mistake is. The above criteria query generates a sql query similar to this

SELECT * FROM lang WHERE ROWNUM=1 ORDER BY SORT_ORDER ASC;

This query always returns the same row, no matter how it is sorted (because ROWNUM=1 always returns the first row). sql then tries to sort the single row :)

Please, correct me if I am missing something. Thanks.

I take it, you're using Oracle. Then your assumption is correct.
Oracle narrows the resultset by applying the where condition. These results are sorted afterwards.

You might be able to do what you want another way:
Try setMaxResults(1) instead of ROWNUM=1.