Solved

Select all records but remove duplicates of email address

Posted on 2004-10-26
246 Views
Last Modified: 2012-05-05
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
Question by:Bill_Harrison
    5 Comments
     
    LVL 6

    Expert Comment

    by: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
    0
     
    LVL 6

    Expert Comment

    by: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
    0
     
    LVL 11

    Expert Comment

    by: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
    0
     
    LVL 11

    Accepted Solution

    by:
    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
     

    Author Comment

    by:Bill_Harrison
    Sorry for the depaly in getting back. Manythnkas for that.

    Any quick comment on how would you improve performance ?

    Regards
    Bill
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    The steps for moving the system databases to a new location are documented in the following technical article: http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx. However sometimes after the moving process is finished, though SQL i…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now