or you can do this
<cfset tmp= querynew("")>
<cfset queryaddcolumn (tmp, "id", "CF_SQL_integer", ListToArray("1,2,3,4,5,6")
<cfset queryaddcolumn (tmp, "priority", "cf_sql_varchar", ListToArray("1,2,3,,4"))>
<cfquery dbtype="query" name="qhp1">
select *
from tmp
where priority is not null
order by priority
</cfquery>
<cfquery dbtype="query" name="qhp2">
select *
from tmp
where priority is null
</cfquery>
<cfquery dbtype="query" name="qhp">
(select * from qhp1) union all (select * from qhp2)
</cfquery>
<cfdump var=#qhp#>
Main Topics
Browse All Topics
by: _agx_Posted on 2009-11-04 at 06:30:23ID: 25739593
> The data in the priority field is mostly null
> allowing priority 1's to show first, 2's second. etc
You need to change this in your original sql query. Use COALESCE, or your db's version of that function, to change the nulls to a higher number like 999, so they will sort last:
<cfquery name="qpromos" .....>
SELECT COALESCE(Priority, 999) AS SortOrder, Priority, ... other columns....
FROM YourTable
</cfquery>
Then order by the "SortOrder" column instead
<cfquery dbtype="query" name="qhp">
SELECT *
FROM qpromos
ORDER BY SortOrder
</cfquery>