title: How to convert SQL Query using CriteriaQuery
body: <p>I want to convert my sql query from SQL to CriteriaQuery, I have this sql query:</p>
  
  <p>1)</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM table AS t WHERE id = (SELECT MAX(id) FROM table AS t WHERE t.element_id = 354 AND (name &lt;&gt; 'foo' OR (name = 'bar' AND event = 'foo')));
  </code></pre>
  
  <p>2)</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT tr1.*
  FROM table AS tr1
  INNER JOIN (SELECT MAX(id) AS id FROM table AS tr WHERE tr.element_id = 354 AND (name &lt;&gt; 'foo' OR (name = 'bar' AND event = 'foo'))) AS tr2
  ON tr1.id = tr2.id;
  </code></pre>
  
  <p>How is the best way to do this ?</p>
  
  <pre class="lang-java prettyprint-override"><code>public Predicate createsubqueryDateGreaterThanTest(CriteriaBuilder cb, Root&lt;? extends Entity&gt; root, Date inputDate){         
          // create the outer query
          CriteriaQuery&lt;Date&gt; cq = cb.createQuery(Date.class);
  
          Root&lt;Table&gt; rootQuery = cq.from(Table.class);
          Subquery&lt;Table&gt; sub = cq.subquery(Table.class);
          Root&lt;Table&gt; subRoot = sub.from(Table.class);
          //sub.select(subRoot);
          cq.multiselect(rootQuery);
          sub.select(subRoot)
          //cq.multiselect(rootQuery.select(cb.max(Table_.elementId)));
          //sub.select(cb.greatest(cb.get(Entity_.element).get("id")));
          //cq.multiselect(subRoot.select(cb.greatest(Table_.elementId)))
              .where(
                      cb.greaterThanOrEqualTo(subRoot.get(Table_.date), inputDate),
                      cb.equal(root.get(Entity_.element).get("id"), subRoot.get(Table_.elementId)),
                      cb.notEqual(subRoot.get(Table_.name), "foo"),
                      cb.or(
                           cb.equal(subRoot.get(Table_.name), "bar"),
                           cb.and(cb.equal(subRoot.get(Table_.event), 'foo'))
                          )
                  );
  
          return cb.exists(sub);
      }
  </code></pre>
  
  <p>The query runs but the result is incorrect.</p>