duplicates in list

[GUJUm0deL]

How do scrub out duplicates in a list?

This is what I am doing:

1) I first query the dB
<cfquery name="getLocations">
select country, state, city
from locations
</cfquery>

2) then take the records and put them in a 'quotedvaluelist' list.
<cfset CountryList = #quotedvaluelist(getLocations.Country)#>

<cfset StateList = #quotedvaluelist(getLocations.State)#>
<cfset CityList = #quotedvaluelist(getLocations.City)#>

Now the problem is the the Country and State have duplicates in which i'd like to scrub out from the list, whats the best way I can do that?

The alternative is having 3 seperate queries (one that returns a distinct country, state and city) and then place them in my list. I'd like to avoid running 3 queries when i can do that in one.

Any ideas?

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[webmigit]

The best way, I guess would to be to cycle through the list and add each unique element to a new list.

<cfset CountryList = quotedvaluelist(getLocations.Country)>
<cfset StatesList = quotedvaluelist(getLocations.State)>
<cfset CitiesList = quotedvaluelist(getLocations.City)>

<cfset uniquestates="">
<cfloop list="#stateslist#" index="i">
  <cfif not listfindnocase(uniquestates,i)>
    <cfset uniquestates=listappend(uniquestates,i)>
  </cfif>
</cfloop>

<cfset uniquecities="">
<cfloop list="#citieslist#" index="i">
  <cfif not listfindnocase(uniquecities,i)>
    <cfset uniquecities=listappend(uniquecities,i)>
  </cfif>
</cfloop>

An SQL Solution might be to select each field from the table as subqueries of one query..

select distinct co.country, st.state, ci.city
from (select distinct country from locations) co
(select distinct state from locations) st
(select distinct city from locations) ci

But that might not work as well as one might hope.. but it should be an alternative.

 

[r937]

webmigit, nice try on the sql, but it would be quite inefficient because of cross join effects

guju, how about query-of-query?

or i could offer a more efficient query

r937.com | rudy.ca

 

[webmigit]

Rudy I would like to see a more efficient query.. That's the best I had and I knew it was suffering. I've learned a lot of sql from you over the years.

QofQ's are still so very disappointing, they lack such baseline functions.. Certain string functions for instance and I'd say they've been around long enough that its sad they still are where they are.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[r937]

select 'country' as source
     , distinct country 
  from locations
union all     
select 'state'
     , distinct state 
  from locations
union all
select 'city'
     , distinct city 
  from locations

then use query of query three times with a WHERE clause on the source column

r937.com | rudy.ca

 

[GUJUm0deL]

Thanks for the help guys.

Rudy, using your SQL, i'd still have to do a query-of-query 3 times; which would be more effective: that method or simply running three quesries (one that get distinct country, distinct state and distinct city)?

Would something like this work:
<cfquery name="getLocations">
select country, state, city
from locations
</cfquery>

<cfoutput query="getLocations" group="country">
<cfset CountryList = #quotedvaluelist(getLocations.Country)#>
</cfoutput>

<cfoutput query="getLocations" group="State">
<cfset StateList = #quotedvaluelist(getLocations.State)#>
</cfoutput>


____________________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

well, with my single UNION query, you'd need 3 query-of-queries only if you needed to construct lists of values (using QuotedValueList)

if you were just going to generate dropdowns (or something similar), you could use 3 CFOUTPUTs instead, with the appropriate CFIF inside

make sense?

r937.com | rudy.ca

 

[GUJUm0deL]

Rudy, yeah that makes sense. I will play around with that this weekend.

____________________________________
Just Imagine.

http://www.imajinarts.com