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> On Mon, Aug 2, 2010 at 3:00 PM, Richard White <rich...@j7is.co.uk> wrote: > > thanks to all posts, and thanks Ria, i like this method :) > > > 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> > > > > >> 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. > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:336007 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
