Criteria projections order by

vsrinivasan · **Joined:** Fri Aug 13, 2004 7:27 pm **Posts:** 6

hello all
iam trying to generate a SQL using a hib criteria...the desired sql is

select A.COL1 from Atable A group by COL1 order by count(A.ID) desc

with the criteria i can get both the column A and count but not just the column A. The reason i want only the column A is because i want to use that in a subquery in criteria. Any input is appreciated . thanks

Using hib 3.1.3.

passos · **Posted:** Fri Oct 27, 2006 3:49 pm

Use alias to your projection count

Code:

.add( Projections.count("YOUR_ATRIBUTE").as("ALIAS_NAME") )

And add Order of name the your alias

Code:

.addOrder( Order.asc("ALIAS_NAME") )

vsrinivasan · **Joined:** Fri Aug 13, 2004 7:27 pm **Posts:** 6

i have tried that but it fetches both the column and count like
select col1,count(A.id)....
which i cant feed into a criteria subquery. i tried using a ResultSetTransformer but as expected its applied to resultset after execution of the query not before...