Cameron,
You may have to apporach this differently...by breaking up your query into
two separate ones. As you've found, simply using the DISTINCT keyword will
still give you duplicates, because of the fact that the not all the data in
each column is exactly duplicated for each record. It would be different it
each field in a record was duplicated exactly.
<cfquery name="" datasource="">
select DISTINCT email, PK
from YourTable
</cfquery>
<cfset Key = query.PK>
<cfquery name="" datasource="">
select fname, lname, rank,PK
from YourTable
WHERE PK = #Key#
</cfquery>
That might work....not super elegant, but hey...I'm not a 'super CF guru'
either ;)
Judith
Cameron Childress put into words:
>1) Eliminate rows containing duplicate email addresses.
>2) In case that a row is eliminated, the selected row should be the top row
>when ordered in this fashion: "ORDER BY rank, lname, fname, pk".
>
>The valid result in this case would be the following set of data:
>"13,24,35,68"
Judith Taylor
ICQ: 67460562
Freelance ColdFusion Developer - Athens, OH
Friends don't let friends code before coffee.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
