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)

Leigh · Accepted Answer · 2013-04-08T03:12:16.643

4

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.)

edited Apr 08 '13 at 03:12

answered Apr 07 '13 at 21:17

Leigh

28,765
10
55
103

after some thought I was down to the two options, and ended up implementing it in the db query rather than the QoQ, as it is probably to correct way of dealing with this. – Daniel Apr 08 '13 at 00:07
@Daniel - Yes, if the source is a db query, it is more efficient to do this at the db level, avoiding the extra `union`and QoQ. – Leigh Apr 08 '13 at 03:13
It is usually more efficient to do this sort of thing at the db level, but every once in awhile using "worst practices" yields better results. – Dan Bracuk Apr 08 '13 at 13:29
True, but this is not one of them ;-) Databases are far better at this type of operation and do not incur the extra overhead of building a duplicate query in memory. But it never hurts to examine the available options to see which performs best and "why". – Leigh Apr 08 '13 at 19:00

score 2 · Answer 2 · answered Apr 07 '13 at 21:21

QoQ on both ColdFusion and Railo have a very limited SQL vocab, and there's nothing that deals with how to collate nulls. So as @Leigh has suggested, add another column - without any nulls - which represent the sorting you want.

Or, better, if possible deal with all this in the DB. Obviously this is not always possible (as the record set you're querying might not have come from a DB in the first place ;-)

score 0 · Answer 3 · answered Apr 08 '13 at 00:13

...there was one more way, but it relies on values being NULL and not empty ''. I'm going from memory here, but this is essentially it, using || only works if the value is non-null, so using the null values descending sort first, I get the values at the end.

 <cfquery>
    SELECT *, '1' || #sortCol# as isNull
    FROM table
    ORDER BY isNull desc, #sortCol#
 </cfquery>

Note I'm not actually advocating the use of this and I'm not sure if CF would handle it the same way

ColdFusion (Railo) QoQ - Nulls Last

3 Answers3