How can I generate the following SQL using Critieria?
The key issue is that I need to join on the result of the subquery on submissionid but also need to include the dcn column so that it can be used to sort the result. A submission has many submissionOccurrences and we need to sort on the dcn of the very lastest occurence.

SELECT * from submission sub
JOIN (
select max(so1.occurrence_start_tmstmp), so1.dcn, so1.submission_id
from submission_occurrence as so1 group by so1.dcn, so1.submission_id
) so
ON sub.submission_id = so.submission_id
where sub.submission_id between 32000 and 33000 order by so.dcn asc;

I've gotten close using the following but it never generates a join on the subcriteria like I would expect. It simply generates an equals on submissionId rather than joining and I get an error because the projection includes multiple columns.

DetachedCriteria subCriteria = DetachedCriteria.forClass(SubmissionOccurrence.class, "latestOccurrence");

ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.max("latestOccurrence.occurrenceStartTimestamp"));
projectionList.add(Projections.groupProperty("latestOccurrence.submissionId"));
projectionList.add(Projections.groupProperty("latestOccurrence.dcn"));

subCriteria.setProjection(projectionList);

criteria.add(Subqueries.propertyEq("submissionId", subCriteria));



Thanks,