asked on

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

Duane Lawrence

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

Duane Lawrence

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

rdrunner

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

ASKER CERTIFIED SOLUTION

rdrunner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill_Harrison

ASKER

Sorry for the depaly in getting back. Manythnkas for that.

Any quick comment on how would you improve performance ?

Regards
Bill