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