I assume you are already looping over the results? So you could build a list of distinct values, within this loop, record at a time.
i.e. treat parent_answer as a list, for each item in this list do a ListFind comparing to your distinct value list. (will be empty to start with) If you find the value in the distinct value list then move on to the next item in the parent_answer list If you do not find it in the distinct value list then ListAdd and move on to the next item in the parent_answer list. The efficiency of this will depend on how many distinct items you have in your data. Another approach is to normalise this data on your database - i.e. parent_answer becomes a sub table - then a distinct query will do the job. Michael Traher Systems Manager ICLP (London) -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 02 September 2003 17:35 To: CF-Talk Subject: SQL or CF Code help with extracting distinct values from a list Hi there, Trying to think of the best way to do this and I know the only way I can think of it will cause the web server to fall over due to the amount of data..... Problem..... Got a query returning something like this RESPONSE_ID ANSWER PARENT_ANSWER 1 Hate Bluetit, Sparrow 2 Love Sparrow 3 Don't care Bluetit, Crow 4 Hate Crow, Budgie What I need is SQL or CF code that I can run on the PARENT_ANSWER list that will give me all the distinct members of a list. ie the answer I would want from this would be 'Bluetit, Sparrow, Crow, Budgie' The records will be in the order of anything from a couple of thousand to a couple of hundred thousand... I've thought of using ValueList(query.PARENT_ANSWER) passing into a UDF that would extract the distinct values, but I know for a fact that in some of my high volume reports that using this sort of methid brings CF/WebServer to complete standstill.. (pretty obvious really, but it was a bad experience and I dont want to go there again, plus the solution will not work in this case) Anybody have a better method of extracting the distinct ones from a list? BTW CF5 and Oracle 8.1.7 Cheers in advance... DC ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
