hql get objects where certain property is unique

Question

I am trying to perform an hql query which returns a list of objects with distinct property value. Following is my pseudo code:

string hql = @"select distinct m from Merchandise m
               where m.Serial is unique"

I am using Castle ActiveRecord on top of NHibernate. I have spent half a day on this problem but couldn't find the correct HQL syntax to do it. Can someone tell me what to do?

Answer 1

Something like this should do the trick:

string hql = @"
     from  Merchandise m
     where not exists (
           from  Merchandise other 
           where m.Serial = other.Serial 
           and   m.Id <> other.Id
           )";

This assumes the Id of Merchandise is just a property called Id.