HQL sorting records by using greatest of 2 columns

For each row in my database开发者_如何学Go, I have 2 columns, say id and A. Sometimes A can be null. I want to sort all records by greatest of its id and A value, but if A is null, it will be ignored and the column will be sorted by id instead.

I'm using Hibernate and database as MySQL. So far, my HQL is like

select i from Item as i order by GREATEST(id, a)

it is working fine except for the records with A=null. For those values, they appear at the end of the returning results (they're supposed to be sorted using their id as key).

How to write such an HQL statement?

---

Somebody solved it with Case..When..End, works perfectly.

select i from Item as i order by CASE WHEN a = null THEN id ELSE GREATEST(id, a) END DESC