Hi Mike.... cheers, but not sure your method will work for me, comments as below....
>> 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. No, I have a query of all the answers for a given question and I am doing COUNT to get the number of responses for each answer.... although each answer has to be matched up with the parent answer of each individual response (ie it's a questionnaire that people fill in, the whole thing is easy as long as the PARENT ANSWER is only one value, but when it gets to checkboxes.....) >> The efficiency of this will depend on how many distinct items you have in your data. At a max, there would be about twenty distinct answers, leading to god knows how many combinations if the PARENT QUESTION was a checkbox question (ie multiple values can be selected) >> 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. Just for background info really, this is a bit of a sore point with me at the moment, the data was originally normalised, but for the large volume reports, retrieving the answers using the normalised database was taking around an hour for the bigger reports (the reports are cached daily so I wasn't running the query all the time)... this just wasn't acceptable as it needs to be a web interface to start a report.... So we all decided (group brainstorm) that it would be better to have a chron job that dumped all the answers on a daily basis into a static single table that could be queried easily in one go (and also solved the archiving issue we had)... then we introduced PARENT ANSWERS and I'm kinda stuck again.... Does that make sense? Cheers all for the brain time. DC -----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 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
