nhibernate - sort null at end

Question

Using NHibernate from C# and only HQL (not SQL) in a way that is compatible with MS SQL Server 2005/2008 (and preferably Oracle).

Is there a way to write the order by clause so that nulls will sort at the end of the query results while the non-null results will be sorted in ascending order?

Based on the answer to the question referenced by nickf the answer is:

select x from MyClass x order by case when x.MyProperty is null then 1 else 0 end, x.MyProperty

Answer 1

I don't know if this helps or not, but there's another question asking the same thing about how to do this with MySQL.

Perhaps the same logic could be applied to HQL?

---

edit: this got accepted, so apparently, yes it can.

Here's the accepted answer from that question (by Bill Karwin):

SELECT * FROM myTable
WHERE ...
ORDER BY CASE WHEN myDate IS NULL THEN 1 ELSE 0 END, myDate;

Answer 2

At one point I just gave up and fixed the sort order in my collection class.

Since I was just moving NULLs all I had to do was peel off the nulls at the beginning of the collection and append them to the end.

With a bet of cleaver coding, it can even be done on an array.

Nevertheless, that ORDER BY CASE is a cleaver and readable trick.