Getting Distinct ID's

[Ocean West]

Love the DISTINCT function of the SQL but trying to figure out how to get the correct set of records.

In a normalized file

member ----< email

there could be one or more duplicate emails in that table.

suppose I am already on the FOUND set in Members, i want a unique list of email addresses - or rather its ID's - i want to go to the related email table keeping the first instance of an email and omitting subsequent duplicates.

I need to end up on the email table so it can export the desired unique found set, that relate to the FOUND set in members.

Savvy?

Thanks

Stephen

[mr_vodka]

Stephen, you would first a script that you capture your IDs for the found set of members. ( or copy all technique, CF, calc, etc )

SEt Variable [ $$foundset; "" ]

Go to Record [ First ]

Loop

Set Variable [ $$foundset; List ( $$foundset; contact::ID ) ]

Go to Record [ Next; Exit after last ]

End Loop

Set Variable [ $$foundset; Substitute ( $$foundset; ¶; "," ) ]

Then you could use something like the following:

ExecuteSQL("SELECT MIN(ID) from email WHERE contactID IN( " & $$foundset & " ) GROUP BY emailaddress"; ""; ""; "" )

[Ocean West]

that won't work because my ID is actually is using get ( UUID )

[mr_vodka]

I think it should still work but if using UUID, since it is text, you would need to to slightly alter the loop script.

SEt Variable [ $$foundset; "" ]

Go to Record [ First ]

Loop

Set Variable [ $$foundset; List ( $$foundset; "'" & contact::ID&"'" ) ]

Go to Record [ Next; Exit after last ]

End Loop

Set Variable [ $$foundset; Substitute ( $$foundset; ¶; "," ) ]

[mr_vodka]

Yeah it should work. Here is a demo file.

contacts.zip

[RalphL]

Kevin Frank"s latest BLOG has a demo file that includes some custom functions which will produce a string that is properly formatted for use with the IN clause. This might solve your problem.

[Ocean West]

Thanks Guys - this did work. I will review Kevin's CF to. however i know his has that iteration limit.

However I doubt i'd ever reach 10k iterations.