Hibernate version:
NHibernate 1.0.3.0

Name and version of the database you are using:
SQL Server 2000/2005 Express (it does it on both)

I've read the docs and FAQ about this topic and came up with the following HQL query in order to sort by collection size:

SELECT tip
FROM Tip tip LEFT JOIN tip.Comments cmt
GROUP BY tip
ORDER BY count(cmt)

However, I get this error:
Column 'dbo.TIP.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Sure enough, looking at the generated SQL, all of the Tip table's fields are in the select, but not the group by, so I see why it fails:

select tip0_.ID as ID, tip0_.Name as Name, tip0_.Views as Views, tip0_.Title as Title, tip0_.UserId as UserId, tip0_.Created as Created, tip0_.Votes as Votes, tip0_.Tip as Tip, tip0_.Updated as Updated from dbo.TIP tip0_ left outer join dbo.TIP_COMMENT comments1_ on tip0_.ID=comments1_.TipId group by tip0_.ID order by count(comments1_.ID)

Any tips on fixing this? I'd like to return the whole object and not have to specify all of the properties, but is there a way?

Thanks for any help.

I don't think that such a query can function in any RDBMS

you can use smth like:

SELECT tip
FROM Tip tip
ORDER BY size(tip.Comments)

This will be translated to:

select ...
from tip
order by (select count(*) from comments where ...)

Hope it helps.

I have the same problem.
NHibernate 1.2.0.4000 and SQL Server 2005 Express

HQL query:
select _Client
from KBI.DAO.Client as _Client
order by size(_Client.Claims) asc

translated in SQL query:
select client0_.ID as ID1_,
client0_.DOC_TYPE_CODE as DOC2_1_,
client0_.CATTYPE_CODE as CATTYPE3_1_,
client0_.FULLNAME as FULLNAME1_,
client0_.LEGAL_STATUS as LEGAL5_1_,
client0_.DOC_SER as DOC6_1_,
client0_.DOC_NO as DOC7_1_,
client0_.DOC_DATE as DOC8_1_,
client0_.SHORTNAME as SHORTNAME1_
from CL_T_CLIENT client0_
order by count(*) asc

This SQL query can not execute because
"Column 'CL_T_CLIENT.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

If I use this HQL query:
select _Client
from KBI.DAO.Client as _Client
group by _Client
order by size(_Client.Claims) asc

this query translated in:
select client0_.ID as ID1_,
client0_.DOC_TYPE_CODE as DOC2_1_,
client0_.CATTYPE_CODE as CATTYPE3_1_,
client0_.FULLNAME as FULLNAME1_,
client0_.LEGAL_STATUS as LEGAL5_1_,
client0_.DOC_SER as DOC6_1_,
client0_.DOC_NO as DOC7_1_,
client0_.DOC_DATE as DOC8_1_,
client0_.SHORTNAME as SHORTNAME1_
from CL_T_CLIENT client0_
group by client0_.ID
order by count(*) asc

This SQL query can not execute because
"Column 'CL_T_CLIENT.DOC_TYPE_CODE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

If I use this HQL query:
select _Client from KBI.DAO.Client as _Client
left join _Client.Claims as _Claims
group by _Client
order by count(_Claims) asc

this query translated in:
select client0_.ID as ID1_,
client0_.DOC_TYPE_CODE as DOC2_1_,
client0_.CATTYPE_CODE as CATTYPE3_1_,
client0_.FULLNAME as FULLNAME1_,
client0_.LEGAL_STATUS as LEGAL5_1_,
client0_.DOC_SER as DOC6_1_,
client0_.DOC_NO as DOC7_1_,
client0_.DOC_DATE as DOC8_1_,
client0_.SHORTNAME as SHORTNAME1_
from CL_T_CLIENT client0_ left outer join CM_T_CLAIM
claims1_ on client0_.ID=claims1_.CLIENT_ID
group by client0_.ID
order by count(claims1_.ID)asc

This SQL query can not execute because
"Column 'CL_T_CLIENT.DOC_TYPE_CODE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

I think, that a mistake in translation Group by _Client in SQL expression, the necessary fields are specified not all. If in the last SQL query add all necessary fields in Group by, query executed correctly.

P.S. Sorry for my bad english.