Jochem van Dieten wrote: > > Actually, there is if your database can do a little bit of maths and > subqueries in the ORDER BY clause. But it is a bit tricky and get's out > of hand when the number of elements in the list get's too long because > you need to develop a power series to sort on :) > > ORDER BY ( 0 > <cfloop from="1" to="#ListLen(titleIDs)#" index="i"> > + (SELECT MAX(TitleID FROM tblJobTitle))^(ListLen(titleIDs) - i) * > Round(Abs(tblJobTitle.TitleID - i)/(Abs(tblJobTitle.TitleID - i) + 1)) > </cfloop> ) DESC > > I doubt this will be usefull IRL, but it should work once you sort out > the typo's you will inevitably get. Conversion to a stored procedure > will help as well, you will be able to get the SELECT MAX(TitleID FROM > tblJobTitle) out of the loop.
Actually, it can be done easier because you are only interested in true/false and not in closeness, which was the original purpose of the code. ORDER BY ( 0 <cfloop from="1" to="#ListLen(titleIDs)#" index="i"> + (2^(ListLen(titleIDs) - i) * Round(Abs(tblJobTitle.TitleID - i)/(Abs(tblJobTitle.TitleID - i) + 1)) </cfloop> ) DESC Would be interesting to know which one of the proposed solutions is the fastest for your dataset. Jochem ______________________________________________________________________ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
