I just noticed that my reply didn't come through earlier... must have been when the mail server was acting up...
My thought was that it looks like he's wanting to order by a string version of the Primary Key rather than a numeric sort. Something like: 1, 10, 11, 12, 13, 14, 15, 2, 22, 3, 343, 4, 40, 42, 555, 70, 6 If that's the case, let the SQL do the work and use a Convert (not tested for syntax, but I've done this in reverse with SQL Server 7): SELECT Name, Convert(Key, VarChar(20) as TextKey >From Table Order By TextKey .. just a thought ;) Hatton -----Original Message----- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 9:55 PM To: CF-Talk Subject: RE: ORDER BY question Ok, I'm an idiot... I just thought of a MUCH faster way to do this.... <!--- create a list with commas at both ends ---> <cfset newOrderList = ",#orderList#,"> <!--- this query is in ORACLE syntax, but can be done for any DB, ORDER BY orders by how far in it found the index in your order by string. ---> <cfquery name="" ...> SELECT * FROM myTable WHERE ID IN (#orderList#) [or whatever here] ORDER BY instr('#newOrderList#',',' | idColumn | ',') </cfquery> +-----------------------------------------------+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +-----------------------------------------------+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -----Original Message----- From: Bryan Love [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 6:45 PM To: CF-Talk Subject: RE: ORDER BY question This will do it, but keep a close eye on the processing time of the server, and make sure if the list gets long that you are using a properly indexed database. customTagName.cfm ------------------------------------------------ <cfparam name="attributes.sortOrder" default=""> <cfset sqlBase = "SELECT * FROM myTable"> <cfif listLen(attributes.sortOrder) GT 1> <cfset sqlString = sqlBase & " WHERE myColumn = #replace(attributes.sortOrder,","," UNION ALL #sqlBase# WHERE myColumn = ","all")#"> <cfelseif attributes.sortOrder NEQ ""> <cfset sqlString = sqlBase & " WHERE myColumn = #attributes.sortOrder#"> <cfelse> <cfset sqlString = sqlBase> </cfif> <cfquery name="" ...> #preserveSingleQuotes(sqlString)# </cfquery> ------------------------------------------------ notice that in the above you are simply creating a SELECT statement for each item in the specified SORTORDER list and UNIONing them all together. I chose UNION ALL because it is faster than UNION... UNION filters out duplicate rows... +-----------------------------------------------+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +-----------------------------------------------+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -----Original Message----- From: Paul Ihrig [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 28, 2002 11:59 AM To: CF-Talk Subject: ORDER BY question ok. ASC & DESC don't work on this one. i want to be able to ORDER BY in a specific order such as ORDER BY tblJobTitle.TitleID='25,26,28,4' is that possible? if not how else could i do it... <!--- tblJobTitle.TitleID Partner=25 Principal=26 Senior Associate=28 Associate=4 ---> Thanks -paul ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
