How can we add a new row in middle of a cfquery result?

Question

I have a query result set from cfquery. I just want to add a new after a particular row number. But When tried each time it inserts the row at the end.

Is there any way I can insert row at the middle of the query?

 <cfquery datasource="cse" name="abc">
    select * from grade 
 </cfquery>

 <cfset i = 0>
 <cfloop query="abc">
   <cfset i = i+1>
   <cfif i eq 2>
      <cfset queryAddRow(abc)>
   </cfif>  
 </cfloop>

Answer 1

You cannot, easily. You have a coupla options.

<cfquery name="resultSet" dbtype="query">
    SELECT col1, col2, etc
    FROM yourQuery
    WHERE [somecondition matching the "top" rows]

    UNION

    SELECT 'value' AS col1, 'value' AS col2, etc

    UNION

    SELECT col1, col2, etc
    FROM yourQuery
    WHERE [somecondition matching the "bottom" rows]
</cfquery>

Or you could simply loop over the original query, building a new query, using queryNew(), queryAddRow() and querySetCell(). At the appropriate point in the loop... add the row you want to insert, then continue adding the rest of them.

There's no elegant way I can think of.