Select all records but remove duplicates of email address

Hi

Trying to do a mailout from customer table, tried to use SELECT DISTINCT but this didn't work.
The customer table has duplcates of the email address so I want to extract the one used in the most resent last_visit field.

This is something like what I'm after:

SELECT
      last_visit,
      CUSTOMER_ID,
      'http://www.x.com/orders/email-sub.asp?email=' + email_address AS opt_out_link,
      email_address,
      title,
      first_name,
      surname,
      tel
FROM
      ar_customer
WHERE  
      (opt_in_email = 1 AND
      email_address NOT LIKE 'ignore@%' AND
      email_address NOT LIKE '%@x.com' AND
      email_address NOT LIKE '%@y.com' AND
      email_address NOT LIKE '%@z.com')
ORDER BY
      last_visit DESC


Many thanks in advance :-)
Bill
Bill_HarrisonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duane LawrenceCommented:


SELECT
  DISTINCT
     last_visit,
     CUSTOMER_ID,
     'http://www.x.com/orders/email-sub.asp?email=' + email_address AS opt_out_link,
     email_address,
     title,
     first_name,
     surname,
     tel
FROM
     ar_customer
WHERE  
     (opt_in_email = 1 AND
     email_address NOT LIKE 'ignore@%' AND
     email_address NOT LIKE '%@x.com' AND
     email_address NOT LIKE '%@y.com' AND
     email_address NOT LIKE '%@z.com')
ORDER BY
     last_visit DESC

Duane
0
Duane LawrenceCommented:
Doh! I hate it when I don't read everything.  Ok select distinct won't work.  Select into a #temp table, then see if a select distinct will work out of that.

Duane
0
rdrunnerCommented:
The duplicates you have have the same email but are different in the other data. Try this

SELECT
     last_visit,
     CUSTOMER_ID,
     'http://www.x.com/orders/email-sub.asp?email=' + email_address AS opt_out_link,
     email_address,
     title,
     first_name,
     surname,
     tel
FROM
     ar_customer
WHERE  
     CUSTOMER_ID in (
SELECT
     max(CUSTOMER_ID)
FROM
     ar_customer

WHERE  
     (opt_in_email = 1 AND
     email_address NOT LIKE 'ignore@%' AND
     email_address NOT LIKE '%@x.com' AND
     email_address NOT LIKE '%@y.com' AND
     email_address NOT LIKE '%@z.com')
group by email_address
)
ORDER BY
     last_visit DESC
0
rdrunnerCommented:
Ok... Reading this again i must say my code is wrong.. It will only deliver the Custumer with the most recent ID

here is the corrected version.....

SELECT
     last_visit,
     CUSTOMER_ID,
     'http://www.x.com/orders/email-sub.asp?email=' + email_address AS opt_out_link,
     email_address,
     title,
     first_name,
     surname,
     tel
FROM
     ar_customer
WHERE  
     CUSTOMER_ID in (
SELECT
     (Select top 1 CUSTOMER_ID from      ar_customer in
where out.email_address = in.email_address
ORDER BY
     last_visit DESC
)
FROM
     ar_customer out

WHERE  
     (opt_in_email = 1 AND
     email_address NOT LIKE 'ignore@%' AND
     email_address NOT LIKE '%@x.com' AND
     email_address NOT LIKE '%@y.com' AND
     email_address NOT LIKE '%@z.com')
group by email_address
)
ORDER BY
     last_visit DESC

I hope this works... is only a copy and paste job :) Performance could be improved but at least it is working (well i hope)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill_HarrisonAuthor Commented:
Sorry for the depaly in getting back. Manythnkas for that.

Any quick comment on how would you improve performance ?

Regards
Bill
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.