Hi,
I'm currently facing problem with db2 dialect. I'm trying to order by subquery with paging activated as well.
Example:

select a.id ,
(select count( aig ) from AgentInGroup aig where aig.agentGroup =a ) as
agentAlias
from Agent a
order by col_1_0_

which generates sql:
select
*
from
( select
rownumber() over(
order by
col_1_0_) as rownumber_,
agent0_.SLA_ID as col_0_0_,
(select
count(agentingro1_.SLAGM_ID)
from
SL_AGENT_IN_GROUP agentingro1_,
SL_AGENT_GROUP agentgroup2_
where
agentingro1_.SLAG_ID=agentgroup2_.SLAG_ID
and agentingro1_.SLAG_ID=agent0_.SLA_ID) as col_1_0_
from
SL_AGENT agent0_
order by
col_1_0_ ) as temp_
where
rownumber_ between ?+1 and ?

This however is producing SQLGrammarException.
I've used col_1_0_ in hql in porpose couse aliases aren't working with subqueries. I tried with numbers of columns instead of aliases but ordering wasn't right in that case.
What is the solutions for sorting over subqueries, anyone???

hibernate version 3.3.1.GA