Cameron -
As it should. trying this from memory ... please excuse any mistakes
The first step should be making unique combinations of Email and Userid ... since you
want one output row for each
Email and your Userid is your unique key.
select
Email,
min(Userid)
from
YourTable
group by
Email
I chose Min because you have to choose either Min or Max. Next we want the Fname and
Lname for each of those
Userid's. If you could put the above into a View, then you join back to it. If you
DBMS has 'inline Views', that is
a better solution ... 'inlineViews' exist for that duration of the SQL statement. I am
not sure that you have those
... so this solution should work but consume a lot of resources
select
Email,
Fname,
Lname,
Userid
from
YourTable a
where
Userid in (select
Email,
min(b.Userid)
from
YourTable b)
BTW, your looping solution may be more efficient. If you have CF 5. This is a good
place to use QoQ.
First,
select
Email,
Fname,
Lname,
Userid
from
YourTable
Call that query1. Next,
select
Email,
min(Userid)
from
query1
group by
Email
Call that query2, Next combine them
select
query1.Email,
query1.Fname,
query1.Lname,
query1.Userid
from
query1,
query2
where
query1.Userid = query2.Userid
hth
-brian
Cameron Childress wrote:
> Ok, I lied....
>
> It does what I thought it would... My grouped records appear to contain the
> min value from each respective column, and not the entire record as it
> should.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
