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
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
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
