I've run into very few instances where the alpha sorting of query columns has been a bother. However, I just ran into one yesterday.
I wanted to use CFTABLE as a quick and easy way to display an unknown recordset. Using COLUMNLIST from the CFQUERY result wouldn't work - I wanted the 'natural' order of my columns. I found a nifty little method in the query that allowed me to gain that information: GetColumnNames() <cfset ColNameArray = Query.GetColumnNames()> <cfset ColNameList = ArrayToList(ColNameArray)> >From there, I was able to loop over the list and generate my CFTABLE in the correct order. Now, this doesn't restructure the recordset, but rather gives you access to the proper order of column names. Hopefully this will be helpful towards whatever you're trying to accomplish. Oh, and for the record - the other time alpha sorting irks me is when I want to do a quick CFDUMP to see the raw data. Obviously not important to the end application, but it annoys me :) Rob -----Original Message----- From: Todd Mathews Sent: Thursday, September 20, 2007 3:08 AM To: CF-Talk Subject: CFQUERY Results in Natural SQL Order? Does anyone know of a simple way to have the results of a CFQUERY return the results in the natural sql result order instead of always returning everything in a sorted column order? IE: Select waypointName, areaName, directionScope, bufferName from xx Should return the columns in the natural order of the sql query (in this case waypointName,areaName, directionScope,bufferName) instead of the current default alphanumeric column sorting which returns the columns using an alphabetical sort of (areaName,bufferName,directionScope,waypointName). We're currently using the following approach to restore the natural order but would LOVE to find a more simplified solution.. In a perfect world this would be a param option for CFQUERY (think something like resultOrder='NAT' or resultOrder='ALPHA' hopefully this conveys the general idea): <!--- use getMetadata to get the names and data types of the fields ---> <cfset querymeta=getMetaData(getRecs)> <!--- build colList and field name / type query ---> <cfset thisColList = ""> <cfset queryInfo=QueryNew("colName,colType")> <cfloop index="i" from="1" to="#arrayLen(querymeta)#"> <cfset thisColName = querymeta[i].name> <cfset thisColType = UCase(querymeta[i].TypeName)> <cfset thisColList = listAppend(thisColList,thisColName)> <cfset tmp=QueryAddRow(queryInfo)> <cfset tmp=QuerySetCell(queryInfo,"colName",thisColName)> <cfset tmp=QuerySetCell(queryInfo,"colType",thisColType)> </cfloop> The above code restores the natural SQL result order and gets the job done but a more simple solution would be quite welcome. Anyone have any thoughts or no any secrets? Todd ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:288907 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4