hi all,

I have a situation where I need to use an inner join on the same table with group by:
A, B (columns at table MY_TABLE, plus another PK column) and their values:
a, 1
a, 2
a, 3
b, 1
b, 2
b, 3
b, 4

Query:
SELECT a.A,B FROM Table_1 a
INNER JOIN (
select A, MAX(B) maxb
from Table_1
group by [A]
) b ON a.A=b.A AND a.B = b.maxb

That works great and returns what I need, which is the maximum of B for each A column:
A,B
a,3
b,4

How can I achieve this in hibernate?

When using the exact query above with EntityManager.createNativeQuery(String), I get the following exception:
org.hibernate.MappingException: No Dialect mapping for JDBC type: -9

When using that query with EntityManager.createQuery(String) - only replacing name of table with name of class - I get the following exception:
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 61 [SELECT a.A,B FROM MyTable a INNER JOIN (select A, MAX(B) maxb from MyTable group by A) b ON a.A=b.A AND a.B = b.maxb]

What am I doing wrong?

thanks for any thought
ShlomiJ

Any thoughts?

SJ