ok tried brians Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]'instr' is not a recognized function name.
with <cfset newOrderList = ",Partner,Principal,Senior Associate,Associate,"> <cfquery name="Employees" datasource="RPH" cachedwithin="#CreateTimeSpan(0,0,30,0)#"> SELECT LEFT(RTrim(tblEmployee.Lastname),10)+', '+tblEmployee.Firstname as Fullname, tblEmployee.EMPLOYEE_ID, tblEmployee.EmployeeID, tblEmployee.StudioID, tblEmployee.StatusID, tblEmployee.TitleID, tblStudio.StudioID, tblStudio.OfficeID, tblStudio.StudioName, tblOffice.OfficeID, tblOffice.City, tblPhone.PhoneNumb, tblPhone.PhoneTypeID, tblFloor.floor, tblJobTitle.TitleID, tblJobTitle.Title FROM tblEmployee, tblStudio, tblOffice, tblPhone, tblFloor, tblJobTitle WHERE tblEmployee.studioid = tblStudio.studioid AND tblEmployee.TitleID = tblJobTitle.TitleID AND tblOffice.OfficeID = tblStudio.OfficeID AND tblJobTitle.TitleID IN (25,26,28,4) AND tblPhone.EmpResID = tblEmployee.EmployeeID AND tblPhone.PhoneTypeID = '1' <!--- AND tblEmployee.StudioID in (#FORM.StudioID#) ---> AND tblEmployee.FloorID = tblFloor.floorid ORDER BY instr('#newOrderList#',',' | Title | ',') , Fullname; </cfquery> -paul -----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 ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists