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>
</table>

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

Just an alternative to above, but you could take ColdFusion out of the picture and do this solely in the SQL using something like (eg in oracle)

select 'myinsertedvalue' from dual union select myrealvalues from mutable

You could combine with Kens sort column to get full ordering. Assuming you are getting the main query from a DB!

Answer 3

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