RE: Removing Duplicate Values in a List
This is code I use. CFSET LIST = "2,1,6,3,2,99,5,2,1,4,1,1" CFSET UNIQUE_LIST = REReplace(ListSort(LIST,"Numeric"),"([^,])(,\1)*","\1","ALL") CFOUTPUT#UNIQUE_LIST#/CFOUTPUT In order to sort text, just change Numeric to TextNoCase. === Raymond Camden, Principal Spectra Compliance Engineer for Allaire Email : [EMAIL PROTECTED] ICQ UIN : 3679482 "My ally is the Force, and a powerful ally it is." - Yoda -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 9:19 AM To: CF-Talk Subject: Removing Duplicate Values in a List I have a List that is generated from a query, How about do I remove duplicate values within the list? I couldn't find any kind of reference to this anywhere not even in Ben's Bible. Thanks for your help! Jason Larson [EMAIL PROTECTED] 406-728-4422 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Removing Duplicate Values in a List
use the Select Distinct option thats in SQL? Select Distinct COl1,Col2,...etc from My_Table where x=y Jason Lees National Express Ltd E-Mail : [EMAIL PROTECTED] -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: 27 February 2001 14:19 To: CF-Talk Subject: Removing Duplicate Values in a List I have a List that is generated from a query, How about do I remove duplicate values within the list? I couldn't find any kind of reference to this anywhere not even in Ben's Bible. Thanks for your help! Jason Larson [EMAIL PROTECTED] 406-728-4422 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Removing Duplicate Values in a List
I've have tried this and it doesn't work. Here's a little better explaination. cfquery name="Keywords" datasource="#DS#" username="#DSUsername#" password="#DSPassword#" SELECT Keywords FROMPhotos /cfquery !--- Creates a list and Alphabetizes out of the database, i.e. bear,elk,wolve --- cfset KeywordList = ListSort(ValueList(Keywords.Keywords), "textnocase") Know is where I run into a problem. This list will contain duplicate values that I want to replace Any ideas? Thanks for your time Jason Larson [EMAIL PROTECTED] -Original Message- From: Jason Lees (National Express) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 7:31 AM To: CF-Talk Subject: RE: Removing Duplicate Values in a List use the Select Distinct option thats in SQL? Select Distinct COl1,Col2,...etc from My_Table where x=y Jason Lees National Express Ltd E-Mail : [EMAIL PROTECTED] -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: 27 February 2001 14:19 To: CF-Talk Subject: Removing Duplicate Values in a List I have a List that is generated from a query, How about do I remove duplicate values within the list? I couldn't find any kind of reference to this anywhere not even in Ben's Bible. Thanks for your help! Jason Larson [EMAIL PROTECTED] 406-728-4422 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Removing Duplicate Values in a List
Use SELECT DISTINCT . Britta Wingenroth West Coast Web - Original Message - From: "Jason Larson" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Tuesday, February 27, 2001 6:18 AM Subject: Removing Duplicate Values in a List I have a List that is generated from a query, How about do I remove duplicate values within the list? I couldn't find any kind of reference to this anywhere not even in Ben's Bible. Thanks for your help! Jason Larson [EMAIL PROTECTED] 406-728-4422 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Removing Duplicate Values in a List
Jason, In order for the DISTINCT keyword to work, the query must be ordered. This query should work: cfquery name="Keywords" datasource="#DS#" username="#DSUsername#" password="#DSPassword#" SELECT DISTINCT Keywords FROMPhotos ORDER BY Keywords /cfquery cfset KeywordList = ValueList(Keywords.Keywords) Craig -Original Message- From: Jason Larson [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 10:05 AM To: CF-Talk Subject: RE: Removing Duplicate Values in a List I've have tried this and it doesn't work. Here's a little better explaination. cfquery name="Keywords" datasource="#DS#" username="#DSUsername#" password="#DSPassword#" SELECT Keywords FROMPhotos /cfquery !--- Creates a list and Alphabetizes out of the database, i.e. bear,elk,wolve --- cfset KeywordList = ListSort(ValueList(Keywords.Keywords), "textnocase") Know is where I run into a problem. This list will contain duplicate values that I want to replace Any ideas? Thanks for your time Jason Larson [EMAIL PROTECTED] -Original Message- From: Jason Lees (National Express) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 7:31 AM To: CF-Talk Subject: RE: Removing Duplicate Values in a List use the Select Distinct option thats in SQL? Select Distinct COl1,Col2,...etc from My_Table where x=y Jason Lees National Express Ltd E-Mail : [EMAIL PROTECTED] -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: 27 February 2001 14:19 To: CF-Talk Subject: Removing Duplicate Values in a List I have a List that is generated from a query, How about do I remove duplicate values within the list? I couldn't find any kind of reference to this anywhere not even in Ben's Bible. Thanks for your help! Jason Larson [EMAIL PROTECTED] 406-728-4422 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Removing Duplicate Values in a List
cfquery name="Keywords" datasource="#DS#" username="#DSUsername#" password="#DSPassword#" SELECT DISTINCT Keywords FROMPhotos /cfquery This will return a query where there are no Duplicates. Jason Lees National Express Ltd E-Mail : [EMAIL PROTECTED] -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: 27 February 2001 15:05 To: CF-Talk Subject: RE: Removing Duplicate Values in a List I've have tried this and it doesn't work. Here's a little better explaination. cfquery name="Keywords" datasource="#DS#" username="#DSUsername#" password="#DSPassword#" SELECT Keywords FROMPhotos /cfquery !--- Creates a list and Alphabetizes out of the database, i.e. bear,elk,wolve --- cfset KeywordList = ListSort(ValueList(Keywords.Keywords), "textnocase") Know is where I run into a problem. This list will contain duplicate values that I want to replace Any ideas? Thanks for your time Jason Larson [EMAIL PROTECTED] -Original Message- From: Jason Lees (National Express) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 7:31 AM To: CF-Talk Subject: RE: Removing Duplicate Values in a List use the Select Distinct option thats in SQL? Select Distinct COl1,Col2,...etc from My_Table where x=y Jason Lees National Express Ltd E-Mail : [EMAIL PROTECTED] -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: 27 February 2001 14:19 To: CF-Talk Subject: Removing Duplicate Values in a List I have a List that is generated from a query, How about do I remove duplicate values within the list? I couldn't find any kind of reference to this anywhere not even in Ben's Bible. Thanks for your help! Jason Larson [EMAIL PROTECTED] 406-728-4422 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Removing Duplicate Values in a List
cfquery name="Keywords" datasource="#DS#" username="#DSUsername#" password="#DSPassword#" SELECT DISTINCT Keywords FROMPhotos ORDER BY Keywords /cfquery Let the SQL engine do the work. If you select distinct keywords they won't be duplicated. Also might as well let the engine sort them as well. - Original Message - From: "Jason Larson" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Tuesday, February 27, 2001 8:05 AM Subject: RE: Removing Duplicate Values in a List I've have tried this and it doesn't work. Here's a little better explaination. cfquery name="Keywords" datasource="#DS#" username="#DSUsername#" password="#DSPassword#" SELECT Keywords FROMPhotos /cfquery !--- Creates a list and Alphabetizes out of the database, i.e. bear,elk,wolve --- cfset KeywordList = ListSort(ValueList(Keywords.Keywords), "textnocase") Know is where I run into a problem. This list will contain duplicate values that I want to replace Any ideas? Thanks for your time Jason Larson [EMAIL PROTECTED] -Original Message- From: Jason Lees (National Express) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 7:31 AM To: CF-Talk Subject: RE: Removing Duplicate Values in a List use the Select Distinct option thats in SQL? Select Distinct COl1,Col2,...etc from My_Table where x=y Jason Lees National Express Ltd E-Mail : [EMAIL PROTECTED] -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: 27 February 2001 14:19 To: CF-Talk Subject: Removing Duplicate Values in a List I have a List that is generated from a query, How about do I remove duplicate values within the list? I couldn't find any kind of reference to this anywhere not even in Ben's Bible. Thanks for your help! Jason Larson [EMAIL PROTECTED] 406-728-4422 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Removing Duplicate Values in a List
Then in the Query, can you use the DISTINCT function? That way there is no duplicates. -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 10:05 AM To: CF-Talk Subject: RE: Removing Duplicate Values in a List I've have tried this and it doesn't work. Here's a little better explaination. cfquery name="Keywords" datasource="#DS#" username="#DSUsername#" password="#DSPassword#" SELECT Keywords FROMPhotos /cfquery !--- Creates a list and Alphabetizes out of the database, i.e. bear,elk,wolve --- cfset KeywordList = ListSort(ValueList(Keywords.Keywords), "textnocase") Know is where I run into a problem. This list will contain duplicate values that I want to replace Any ideas? Thanks for your time Jason Larson [EMAIL PROTECTED] -Original Message- From: Jason Lees (National Express) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 7:31 AM To: CF-Talk Subject: RE: Removing Duplicate Values in a List use the Select Distinct option thats in SQL? Select Distinct COl1,Col2,...etc from My_Table where x=y Jason Lees National Express Ltd E-Mail : [EMAIL PROTECTED] -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: 27 February 2001 14:19 To: CF-Talk Subject: Removing Duplicate Values in a List I have a List that is generated from a query, How about do I remove duplicate values within the list? I couldn't find any kind of reference to this anywhere not even in Ben's Bible. Thanks for your help! Jason Larson [EMAIL PROTECTED] 406-728-4422 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Removing Duplicate Values in a List
Actually, it is telling you that you need to make your query return exactly what you want. YES, using Distinct or Unique will work, but it is not the best way to get what you want out of the data. Distinct is shortcut to effectively grouping your results... or: "The DISTINCT keyword sorts the results set in ascending order by the first column listed in the DISTINCT list" So it may not give you the exact answer that you are looking for every time. In short, the repeating values are telling you to reword your query, because you didn't ask it quite right the first time. (This is, of course, my opinion... but it works for me.) Heath -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 9:19 AM To: CF-Talk Subject: Removing Duplicate Values in a List I have a List that is generated from a query, How about do I remove duplicate values within the list? I couldn't find any kind of reference to this anywhere not even in Ben's Bible. Thanks for your help! Jason Larson [EMAIL PROTECTED] 406-728-4422 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Removing Duplicate Values in a List
OK, the list can be filtered this way in ColdFusion before you ship it off to the DataBase Machine to run your query... cfset My_Keyword_List = "" cfloop list="#Keywords#" index="Current_Keyword" cfif not listfind(My_Keyword_List,Current_Keyword) cfset My_Keyword_List = listappend(My_Keyword_List,Current_Keyword) /cfif /cfloop But, in the query you show, yes, DISTINCT would be ok in this case, since you are only getting keywords from the database... Heath -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 10:05 AM To: CF-Talk Subject: RE: Removing Duplicate Values in a List I've have tried this and it doesn't work. Here's a little better explaination. cfquery name="Keywords" datasource="#DS#" username="#DSUsername#" password="#DSPassword#" SELECT Keywords FROMPhotos /cfquery !--- Creates a list and Alphabetizes out of the database, i.e. bear,elk,wolve --- cfset KeywordList = ListSort(ValueList(Keywords.Keywords), "textnocase") Know is where I run into a problem. This list will contain duplicate values that I want to replace Any ideas? Thanks for your time Jason Larson [EMAIL PROTECTED] -Original Message- From: Jason Lees (National Express) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 7:31 AM To: CF-Talk Subject: RE: Removing Duplicate Values in a List use the Select Distinct option thats in SQL? Select Distinct COl1,Col2,...etc from My_Table where x=y Jason Lees National Express Ltd E-Mail : [EMAIL PROTECTED] -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: 27 February 2001 14:19 To: CF-Talk Subject: Removing Duplicate Values in a List I have a List that is generated from a query, How about do I remove duplicate values within the list? I couldn't find any kind of reference to this anywhere not even in Ben's Bible. Thanks for your help! Jason Larson [EMAIL PROTECTED] 406-728-4422 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Removing Duplicate Values in a List
The DISTINCT function doesn't work. I have a column within a database that is populated by a number of lists. Here's my query and listsort : cfquery name="Keywords" datasource="#DS#" username="#DSUsername#" password="#DSPassword#" SELECT DISTINCT Keywords FROMPhotos ORDER BYKeywords /cfquery CFSET UNIQUE_LIST = REReplace(ListSort(ValueList(Keywords.Keywords),"textnocase"),"([^,])(,\1)*" ,"\1","ALL") !--- Here's the list it generates: bear,cubs,bird,bear,elk,deer,bird --- My database is in Access, for some reason the DISTINCT Function doesn't work with a column that is populated with a list. Thanks for all your feedback Jason Larson [EMAIL PROTECTED] -Original Message- From: Adkins, Randy [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 8:35 AM To: CF-Talk Subject: RE: Removing Duplicate Values in a List Then in the Query, can you use the DISTINCT function? That way there is no duplicates. -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 10:05 AM To: CF-Talk Subject: RE: Removing Duplicate Values in a List I've have tried this and it doesn't work. Here's a little better explaination. cfquery name="Keywords" datasource="#DS#" username="#DSUsername#" password="#DSPassword#" SELECT Keywords FROMPhotos /cfquery !--- Creates a list and Alphabetizes out of the database, i.e. bear,elk,wolve --- cfset KeywordList = ListSort(ValueList(Keywords.Keywords), "textnocase") Know is where I run into a problem. This list will contain duplicate values that I want to replace Any ideas? Thanks for your time Jason Larson [EMAIL PROTECTED] -Original Message----- From: Jason Lees (National Express) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 27, 2001 7:31 AM To: CF-Talk Subject: RE: Removing Duplicate Values in a List use the Select Distinct option thats in SQL? Select Distinct COl1,Col2,...etc from My_Table where x=y Jason Lees National Express Ltd E-Mail : [EMAIL PROTECTED] -Original Message- From: Jason Larson [mailto:[EMAIL PROTECTED]] Sent: 27 February 2001 14:19 To: CF-Talk Subject: Removing Duplicate Values in a List I have a List that is generated from a query, How about do I remove duplicate values within the list? I couldn't find any kind of reference to this anywhere not even in Ben's Bible. Thanks for your help! Jason Larson [EMAIL PROTECTED] 406-728-4422 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Removing Duplicate Values in a List
CFQUERY DATASOURCE="..." NAME="GetKeywords"... SELECT DISTINCT Keywords FROM Photos ORDER BY Keywords /CFQUERY CFSET variables.UniqueList = ValueList(GetKeywords, Keywords) should work -- Aidan Whitehall [EMAIL PROTECTED] Netshopper UK Ltd Advanced Web Solutions Services http://www.netshopperuk.com/ Telephone +44 (01744) 648650 Fax +44 (01744) 648651 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
