title: How to create hibernate criteria with DISTINCT in this case
body: <p>I am using hibernate criteria to create postgres query:</p>
  
  <pre><code>criteria.addOrder(Order.asc(property).ignoreCase()).setProjection(
                              Projections.distinct(Projections.property("name")));
  </code></pre>
  
  <p>which generates sql similar to below:</p>
  
  <pre><code>select DISTINCT name from table1 order by lower( name) asc;
  </code></pre>
  
  <p>and when this sql gets executed gives below error:</p>
  
  <pre><code>ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
  </code></pre>
  
  <p>I know this is something against the postgres rule, but when i looked into the error, i found that we should not use any function at order by clause. If its used then we need to have two work arounds:</p>
  
  <ol>
  <li><p>use nested select as below:</p>
  
  <p><code>select name from (select distinct name from table1) as aliasname order by lower(name) asc;</code></p></li>
  <li><p>use lower function in both select and order by clause.</p>
  
  <p><code>select DISTINCT lower(name) from table1 order by lower(name) asc;</code></p></li>
  </ol>
  
  <p>So for the fix, I will prefer a solution into hibernate side. how to create a criteria such that i can get any of the above two sqls.</p>
  
  <ul>
  <li><p>how to create criteria to get nested select.</p></li>
  <li><p>how ensure from criteria that the function used in order by must be applied in select also</p>
  
  <ul>
  <li>is ther any better solution on hibernate or DB side (<strong>without using hardcoded sql,  createQuery() or HQL</strong>).</li>
  </ul></li>
  </ul>
  
  <p>thanks for your suggetions in advance.</p>