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?

Improve company productivity with a Business Account.Sign Up

x
 
rdrunnerConnect With a Mentor Commented:
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.