I never give advice, especially with SQL...so if it doe snot work...uh...
Have you treid something along these lines?
SELECT DISTINCT T1.email,
(SELECT pk FROM table WHERE pk = T1.pk) AS ThisEmail
FROM table1 T1
ORDER BY T1.rank, etc...
Not in front of a workstation so I cannot test. But what you want is a list
of distinct emails, and also (the subquery) there PK num?
Dave
----- Original Message -----
From: "Judith Taylor" <[EMAIL PROTECTED]>
To: "SQL" <[EMAIL PROTECTED]>
Sent: Friday, August 24, 2001 6:46 PM
Subject: Re: ARG - Eliminating dups
> 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
