Hibernate-Criteria: Select distinct column from table ordered by another column -

what want achieve: list of unique ids ordered column, using hibernate criteria.

my code far:

 criteria crt = createcriteria(); //complex method returning criteria                                   //with joined tables, restrictions , ordering  crt.setprojection( projections.distinct( projections.property( "id")));  list<integer> ids = crt.list(); 

what wrong: when set order column let's "date" query:

select distinct id table \\join, where...\\ order date); 

which wrong , results in error:

ora-01791: not selected expression 

my idea how solve it: somehow force hibernate generate query:

select distinct id (select * table \\join, where...\\ order date); 

problems , tried: there createcriteria() method, don't want modify if it's not absolutely necessary. didn't find way use criteria detachedcriteria.

you cannot put subqueries clause criteria.

bug can:

• use hql
• use plain sql
• change query

is id not unique anyway? why not put date select clause? change result, except of column in result.

select distinct date, id table order date 

---

another approach:

select min(date), id table group id order min(date) 

something this:

createcriteria(sometable.class)   .setprojection(projections.projectionlist()     .add(projections.groupproperty("id"))     .add(projections.min("date"), "mindate")   .add(order.desc("mindate"))