title: Inner Join and Group By using Specification in Spring Data JPA
body: <p>I am trying to fetch the employee details whose empltype is clerk and whose joining date is the recent one.</p>
  
  <p>For which  the query looks like following in SQL Server 2008:</p>
  
  <pre><code>select 
   * 
  from 
    employee jj 
    inner join
    (
      select 
        max(join_date) as jdate,
        empltype as emptype 
      from 
        employee 
      where 
        empltype='clerk' 
      group by empltype
    ) mm 
      on jj.join_date=mm.jdate and jj.empltype=mm.emptype;
  </code></pre>
  
  <p>I am using SpringData JPA as my persistence layer using QuerylDSL,Specification and Predicate to fetch the data.</p>
  
  <p>I am trying to convert the above query either in QueryDSL or Specification, but unable to hook them properly.</p>
  
  <p><strong>Employee Entity :</strong></p>
  
  <pre><code>int seqid;(sequence id)
  String empltype:
  Date joindate;
  String role;
  </code></pre>
  
  <p><strong>Predicate method in Specifcation Class :</strong></p>
  
  <pre><code>Predicate toPredicate(Root&lt;employee&gt; root,CriteriaQuery &lt;?&gt; query,CriteriaBuilder cb)
  {
              Predicate pred=null;
              // Returning the rows matching the joining date
              pred=cb.equal(root&lt;Emplyoee_&gt;.get("joindate"));
              //**//
  
  }
  </code></pre>
  
  <p>What piece of code should be written in //**// to convert about SQL query to JPA predicate. any other Spring Data JPA impl  like @Query,NamedQuery or QueryDSL which returns Page also works for me.</p>
  
  <p>Thanks in advance</p>