Hello,
I have the address table and my sql query is following:

select l.community_name,l.city,m.community_count,l.state,l.community_address
from address l
inner join(
select community_name,city,state, count(*) as community_count
from address where state = 'AZ'
group by community_name,city,state
) m
on m.city=l.city and m.COMMUNITY_name=l.community_name and l.state=m.state and m.community_count = 1

Assume that i have Address object mapped to this table. Please let me know the equivalent hibernate query for the same.

Thanks in advance