Search code examples
sqlhibernatehqljpql

MS Sql query to Hibernate HQL

How could I transform the following sql to HQL

select max(id) from mytable where id in (select top 10 id from mytable where mycolumn-value = 1234 order by id)
Solution

  • You cannot do exactly a JPQL equivalent, because it's not possible to use top (or limit, rownum... each database use one different) in a subquery using JPQL.

    But you use something like this:

    select max(mt.id) from mytable mt where mt.id in 
   (select mt2.id from mytable mt2 where mt2.mycolumn-value = 1234 order by mt2.id)

    This query ignore the top part on subquery, it will not affect the expected result.