ColdFusion (Railo) QoQ - Nulls Last

Question

My understanding is that nulls last is not possible with QoQ. How do I trick coldfusion into sorting null values last whether i'm sorting the row ascending or descending?

I've tried using case in the SELECT and ORDER part of query, but looks like CF is not liking it (running on railo)

Answer 1

There may be better options, but one simple trick is to add a column representing sort priority. Assign records with non-null values a higher priority than null values. Then simply sort by the priority value first, then any other columns you want. Since the null values have a lower priority number, they will always sort last.

    <!--- 1 - non-null values 2 - null values --->
    SELECT 1 AS SortOrder, SomeColumn
    FROM   theQuery
    WHERE  SomeColumn IS NOT NULL
    UNION ALL
    SELECT 2 AS SortOrder, SomeColumn
    FROM   theQuery
    WHERE  SomeColumn IS NULL
    ORDER BY SortOrder, SomeColumn ASC

(It is worth noting you could probably do something similar in your database query using order by instead of union.)