I am trying to set the order by clause to the count of the Map collection via HQL. My mapping file looks like this

The Orders Class
<class name="Orders" table="Orders" >
<id name="Id" type="Int64">
<generator class="identity" />
</id>
<property column="Title" type="String" name="Title" length="400" />
<map name="Details" cascade="none" lazy="false">
<key column="OrderId" />
<index column="OrderedBy" type="Int64" />
<one-to-many class="DataLayer.Entities.OrderDetails, PCSBAL" />
</map>
</class>

The OrdersDetails Class
<class name="OrderDetails" table="OrderDetails">
<composite-id unsaved-value ="any">
<key-property name="OrderId" column="OrderId" type="Int64" />
<key-property name="OrderedBy" column="OrderedBy" type="Int64" />
</composite-id>
<property column="OrderedOn" type="DateTime" name="OrderedOn" not-null="true" />
</class>

I want that when I retrieve a list of Orders the list is sorted on the count of the Details collection (map) from the Order Class.

The HQL that I build is as below

from DataLayer.Entities.Orders Orders Where Orders.Id=@id order by count(Orders.Details) asc

Before translation to SQL it gives me the following error 'Incorrect query syntax'.

Is it because NHibernate does not allow you to sort in the fashion that I want it to or am I missing something?

Platform: .NET 2.0 and Sql Server 2005

Appreciate your help....