<p>I'm trying to implement paging using row-based limiting (for example: <code>setFirstResult(5)</code> and <code>setMaxResults(10)</code>) on a Hibernate Criteria query that has joins to other tables.</p>

<p>Understandably, data is getting cut off randomly; and the reason for that is explained <a href="https://developer.jboss.org/wiki/HibernateFAQ-AdvancedProblems#jive_content_id_Hibernate_does_not_return_distinct_results_for_a_query_with_outer_join_fetching_enabled_for_a_collection_even_if_I_use_the_distinct_keyword" rel="noreferrer">here</a>.</p>

<p>As a solution, the page suggests using a "second sql select" instead of a join. </p>

<p>How can I convert my existing criteria query (which has joins using <code>createAlias()</code>) to use a nested select instead?</p>

You can achieve the desired result by requesting a list of distinct ids instead of a list of distinct hydrated objects.
Simply add this to your criteria:
```
```
criteria.setProjection(Projections.distinct(Projections.property("id")));
```
```
Now you'll get the correct number of results according to your row-based limiting. The reason this works is because the projection will perform the distinctness check
as part of
the sql query, instead of what a ResultTransformer does which is to filter the results for distinctness
after
the sql query has been performed.
Worth noting is that instead of getting a list of objects, you will now get a list of ids, which you can use to hydrate objects from hibernate later.