query add row at top of query

hi i have a query which has sort criteria applied at mysql level. i want to add a row at the top of the query after mysql has finished with it. If i use the queryaddrow method it adds the row to the bottom of the query is there anyway to get it to add it as the first item? thanks

<cfquery     name="GetParks" datasource="cfdocexamples"     cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#">       SELECT PARKNAME, REGION, STATE       FROM Parks     Where REGION = 'Southeast Region'       ORDER BY ParkName, State   </cfquery> <cfset temp = querysetcell(GetParks, "PARKNAME", " ", 1)> <cfset temp = querysetcell(GetParks, "REGION", " ", 1)> <cfset temp = querysetcell(GetParks, "STATE", " ", 1)> ----- Excess quoted text cut - see Original Post for more -----

thanks, although would that not just overwrite the data in row 1? ----- Excess quoted text cut - see Original Post for more -----

Probably, though you can easily verify it yourself. One option is using a UNION. Either in a QoQ (or your original database query). Assign a static sort value to each statement. Then ORDER BY that value to produce the desired ordering. SELECT 0 AS SortValue, 'Some Value' ColumnA, 'Some Value' ColumnB UNION SELECT 1 AS SortValue, ColumnA, ColumnB FROM   yourQueryName ORDER BY SortValue       

> i have a query which has sort criteria applied at mysql level. > > i want to add a row at the top of the query after mysql has finished with it. > > If i use the queryaddrow method it adds the row to the bottom of the query > > is there anyway to get it to add it as the first item? You could add the new row in the SQL itself using a UNION statement, couldn't you? Otherwise, you could add it using queryAddRow, querySetCell, etc, but you'd have to figure out how to get things in the order that you want them. You could create a new query, add the row to the new query, then loop through the old query and add each row to the new query. Or you could use queryAddRow and querySetCell with the existing query, but have a sortable field for all rows including the new row, then use query of query to sort the query again. But I'd go with doing this in your SQL if I were you. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite.

You can refer to this link to appendQuery http://www.bennadel.com/blog/114-ColdFusion-QueryAppend-qOne-qTwo-.htm I guess you should go with method one as of my understanding. Posting example here. appendQuery.cfm <cfquery     name="GetParks" datasource="cfdocexamples"     cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#">       SELECT PARKNAME, REGION, STATE       FROM Parks     Where REGION = 'Southeast Region'       ORDER BY ParkName, State   </cfquery> <cfset testQuery = queryNew("PARKNAME,REGION,STATE","varchar,varchar,varchar")> <cfset queryAddrow(testQuery,1)> <cfset test1 = createObject('component','appendQuery')> <cfset resultQuery = test1.QueryAppend(testQuery,GetParks)> <cfdump var="#resultQuery#"> <cfabort> <cfset test1 = createObject('component','appendQuery')> <cfset resultQuery = test1.QueryAppend2(testQuery,GetParks)> <cfdump var="#resultQuery#"> appendQuery.cfc <cffunction name="QueryAppend" access="public" returntype="query" output="false" hint="This takes two queries and appends the second one to the first one. Returns the resultant third query."> <!--- Define arguments. ---> <cfargument name="QueryOne" type="query" required="true" /> <cfargument name="QueryTwo" type="query" required="true" /> <cfargument name="UnionAll" type="boolean" required="false" default="true" /> <!--- Define the local scope. ---> <cfset var LOCAL = StructNew() /> <!--- Append the second to the first. Do this by unioning the two queries. ---> <cfquery name="LOCAL.NewQuery" dbtype="query"> <!--- Select all from the first query. ---> ( SELECT * FROM ARGUMENTS.QueryOne ) <!--- Union the two queries together. ---> UNION <!--- Check to see if we are going to care about duplicates. If we don't expect duplicates then just union all. ---> <cfif ARGUMENTS.UnionAll> ALL </cfif> <!--- Select all from the second query. ---> ( SELECT * FROM ARGUMENTS.QueryTwo ) </cfquery> <!--- Return the new query. ---> <cfreturn LOCAL.NewQuery /> </cffunction> ----- Excess quoted text cut - see Original Post for more -----

thanks to all posts, and thanks Ria, i like this method :) ----- Excess quoted text cut - see Original Post for more -----      > name="GetParks" datasource="cfdocexamples"      > cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> >       SELECT PARKNAME, REGION, STATE      >   FROM Parks >     Where REGION = 'Southeast Region'      ----- Excess quoted text cut - see Original Post for more -----

I know I'm a little late to the game here, but wouldn't the easier way be just to union an explicitly set row? It's a LOT less code and lets the db take the load. <cfquery name="GetParks" datasource="cfdocexamples"> SELECT PARKNAME, REGION, STATE, cORDER = 2 FROM Parks Where REGION = 'Southeast Region' UNION SELECT TOP 1 PARKNAME = 'myName', REGION = 'myRegion', STATE = 'myState', cORDER = 1 FROM Parks ORDER BY cORDER, ParkName, State </cfquery> ----- Excess quoted text cut - see Original Post for more -----