title: criteria query ORDER BY yields error. Is this an SQL-SERVER limitation? How could I order by correctly on a complicated criteria query?
body: <p>I have the following criteria query:</p>
  
  <pre><code>    String cat = "H";
      Criteria criteria = currentSession().createCriteria(this.getPersistentClass()).
              add(Restrictions.ne("category", cat)).
              createAlias("employees", "emp").
              createAlias("emp.company", "company");
      Disjunction disjunction = Restrictions.disjunction();
      for(Region r: regions){
             disjunction.add(Restrictions.eq("company.region", r));
      }
      criteria.add(disjunction);
  
      if(status != null) {
          criteria.add(Restrictions.eq("status", status));
      }
  
      if (period != null) {
          criteria.add(Restrictions.eq("period", period));
      }
  
      criteria.setProjection(Projections.groupProperty("id")) //this line was added to try to "fix" the error, but it still happened.
      criteria.addOrder(Order.asc("id"));
  </code></pre>
  
  <p>I guess a query that explains my criteria query could be: </p>
  
  <pre><code>  select n.* from NOMINATION n
    join NOMINEE i on n.NOM_ID = i.NOM_ID
    join EMPLOYEE e on e.EMP_ID = i.EMP_ID
    join COMPANY c on c.COMPANY_CODE = e.COMPANY_CODE
    where n.CATEGORY_CODE!='H' and (c.REGION_ID = ? or c.REGION_ID = ? or c.REGION_ID = ?) and n.STATUS_ID = ? and n.PERIOD_ID = ?
    order by n.NOM_ID
  </code></pre>
  
  <p>What I am trying to do here, is pretty confusing but for the most part it works except when I add this specific line (though the query works fine):</p>
  
  <pre><code> criteria.addOrder(Order.asc("id"));
  </code></pre>
  
  <p>and then I get error:</p>
  
  <pre><code> java.sql.SQLException: Column "NOMINATION.NOM_ID" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
  </code></pre>
  
  <p>Which I suspect is something that has to do with SQL-SERVER. I am already grouping by <code>id</code>. So what am I doing wrong here, or should I just use HQL?</p>