title: Get rows based on maximum value for subsets of a table
body: <p>I want to filter a table based on the values of one column, then get the maximum value for each of these values.</p>
  
  <p>e.g.</p>
  
  <pre><code>id | value
  -----------
  0  | 10
  0  | 22
  0  | 50
  1  | 33
  1  | 4
  2  | 5
  2  | 23
  2  | 33
  3  | 22
  3  | 50
  </code></pre>
  
  <p>Filter by rows with IDs 2 and 3, then get the maximum of each id</p>
  
  <pre><code>id | value
  -----------
  2  | 33
  3  | 50
  </code></pre>
  
  <p>How do I use that using hibernate?</p>
  
  <p>This is my attempt:</p>
  
  <pre><code>    List&lt;int&gt; ids = ... // Retreived from elsewhere
  
      Disjunction disjunction = Restrictions.disjunction();
      for(int id: ids){
          disjunction.add(Restrictions.eq("id", id));    // Specify which IDs
      }
  
      @SuppressWarnings("unchecked")
      List&lt;Item&gt; items= (List&lt;Item&gt;) sessionFactory.getCurrentSession()
              .createCriteria(Item.class)
              .add(disjunction)
              .setProjection(
                  Projections.projectionList()
                      .add(Projections.max("value"))
                      .add(Projections.groupProperty("id")
                  )
              )
              .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
              .list();
  </code></pre>
  
  <p>This is just giving me the 'id' with the highest value (e.g. 3, not the entire row)</p>
  
  <p>I am trying to do this in a spring mvc app.</p>
  
  <p>Thanks in advance</p>