How do you make an added row from QueryAddRow() the first row of the result from a query?

Question

I am outputting a query but need to specify the first row of the result. I am adding the row with QueryAddRow() and setting the values with QuerySetCell(). I can create the row fine, I can add the content to that row fine. If I leave the argument for the row number off of QuerySetCell() then it all works great as the last result of the query when output. However, I need it to be first row of the query but when I try to set the row attribute with the QuerySetCell it just overwrites the first returned row from my query (i.e. my QueryAddRow() replaces the first record from my query). What I currently have is setting a variable from recordCount and arranging the output but there has to be a really simple way to do this that I am just not getting. This code sets the row value to 1 but overwrites the first returned row from the query.

<cfquery name="qxLookup" datasource="#application.datasource#">
    SELECT xID, xName, execution
    FROM table
</cfquery>

<cfset QueryAddRow(qxLookup)/>
<cfset QuerySetCell(qxLookup, "xID","0",1)/>
<cfset QuerySetCell(qxLookup, "xName","Delete",1)/>
<cfset QuerySetCell(qxLookup, "execution", "Select this to delete",1)/>

<cfoutput query="qxLookup">
    <tr>
        <td>
            <a href="##" onclick="javascript:ColdFusion.navigate('xSelect/x.cfm?xNameVar=#url.xNameVar#&xID=#qxLookup.xID#&xName=#URLEncodedFormat(qxLookup.xName)#', '#xNameVar#');ColdFusion.Window.hide('#url.window#')">#qxLookup.xName#</a>
        </td>
        <td>#qxLookup.execution#</td>
    </tr>
</cfoutput>

Thanks for any help.

Answer 1

I would add some kind of sort order column to your original query, populating it with a fixed value of 1.

<cfquery name="qxLookup" datasource="#application.datasource#">
SELECT xID, xName, execution, 1 as sortorder
FROM table
</cfquery>

Set the value of that column in your synthetic row to a value of 0.

<cfset QueryAddRow(qxLookup)>
...
<cfset QuerySetCell(qxLookup, "sortorder", "0",1)>

Then use query-of-queries to reorder the recordset by the sortorder column.

<cfquery name="qxLookup" dbtype="query">
select xid, xname, execution
from qxLookup
order by sortorder
</cfquery>

Answer 2

Well I have dealt with this problem before, for me the answer was to have 2 seperate queries.

1st, being your normal query, 2nd being the query of queries, then do a union of them, with the qofq being above the normal query, and that should give you results in the order you want.

Something like this:

<cfquery name="table_a_results" datasource="">
select a, b, c
from table_a
</cfquery>

cfset table_b = querynew("a, b, c")
cfset temp = queryaddrow("table_b")
cfset temp = querysetcell(table_b,10)
cfset temp = querysetcell(table_b,20)
cfset temp = querysetcell(table_b,30)

<cfquery name="final_query" dbtype="query">
select a, b, c
from table_b
union
select a, b, c
from table_a_results
</cfquery>

Then using this tool you can put queries in any order you like, but remember to use the order by tag, to change order...