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.

Defective

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>

Trust

thank you. that is better than what I have right now. I was really hoping that I was missing something simple (like an attribute for QueryAddRow - since the default is to add the last row, maybe there is an attribute to make it the first row?). – Defective

@JS - +1 to Ken's suggestion. AFAIK, you are not missing anything and QueryAddRow only allows appending rows the end of the query. – Swop

this works, thanks. I left the row # attribute out and just added them as the default last row then did the QOQ to sort. – Defective

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

Warily

Answer 3

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!

Prober

thanks for this. I use the same query a few different places and don't always need to do this sort so I figured that since I already had it in memory that going the CF route would be simple and 1 less call to the DB. – Defective