title: How to use DISTINCT, GROUP BY, ORDER BY in spring-data-jpa?
body: <p><strong>Beware</strong>: this question has been falsely marked as duplicate. Anyways I found a solution that I will add to my question below, as answers cannot be posted anymore on "duplicate" questions.</p>
  
  <hr>
  
  <p>I want to transform the following <code>sql</code> query into a <code>JPA</code> query using <code>querydsl</code> and spring-data-jpa:</p>
  
  <pre><code>SELECT DISTINCT age FROM `my_table`
    WHERE firstname = 'john' AND lastname = 'doe'
    GROUP BY age
    ORDER BY COUNT(*) DESC
    LIMIT 1
  </code></pre>
  
  <p>Currently I have the following, which just selects all entries by first+lastname:</p>
  
  <pre><code>public interface MyRepository extends JpaRepository&lt;MyEntity, Long&gt;, QueryDslPredicateExecutor&lt;MyEntity&gt; {
  
  }
  </code></pre>
  
  <p>Usage:</p>
  
  <pre><code>@Autowired
  private MyRepository dao;
  
  //select * from my_table where firstname = ? and lastname = ?
  BooleanExpression query = p.firstname.eq(firstname).and(p.lastname.eq(lastname));
  dao.findAll(query);
  
  //TODO how to add group, order and limit?
  </code></pre>
  
  <p>But I now want to group by <code>age</code> field and select the age that occurs the most. But how can I add <code>DISTINCT</code>, <code>GROUP BY</code>, <code>ORDER BY COUNT</code> and <code>LIMIT</code> statements in the querydsl?</p>
  
  <hr>
  
  <p><strong>Solution</strong>: it's not possible directly using <code>CrudRepository</code>, but still easy by using <code>com.querydsl.jpa.impl.JPAQuery</code> as follows:</p>
  
  <pre><code>QPersonEntity p = QPersonEntity.person; //the querydsl definition
  
  int age = new JPAQuery(em)
      .select(p.age)
      .from(p)
      .where(p.firstname.eq(firstname).and(p.lastname.eq(lastname)))
      .groupBy(p.age)
      .orderBy(p.age.count().desc())
      .limit(1)
      .fetchOne();
  </code></pre>