Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

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
0
Bill_Harrison
Asked:
Bill_Harrison
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now