title: Subquery with Inner Join in HQL
body: <p>I want to print title from Movies table which has Foreign key Movie_id in Rating table.In rating table we have to get top 10 results based on movie_id occurrence .Since Limit is not allowed in HQL so <strong>setMaxResults</strong> is used</p>
  
  <pre><code>Query q=session.createQuery("select Title from Movies  as M Inner Join ( SELECT Movie_id, COUNT(*)  FROM Rating  GROUP BY Movie_id ORDER BY COUNT(*) DESC LIMIT 10 ) as R ON M.Movie_id=R.Movie_id").setMaxResults(10);
  </code></pre>
  
  <p><strong>Exception is:</strong> <br></p>
  
  <pre><code>java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 59 [select Title from com.rahul.model.Movies  as M Inner Join ( SELECT Movie_id, COUNT(*)  FROM com.rahul.model.Rating  GROUP BY Movie_id ORDER BY COUNT(*) DESC LIMIT 10 ) as R ON M.Movie_id=R.Movie_id]
  </code></pre>
  
  <p>Since HQL doesn't support subquery how to achieve it ?</p>