[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Select all records but remove duplicates of email address

Posted on 2004-10-26
5
Medium Priority
?
262 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
Comment
Question by:Bill_Harrison
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12414007


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
ID: 12414029
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
ID: 12414036
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:
rdrunner earned 750 total points
ID: 12414226
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
ID: 12430580
Sorry for the depaly in getting back. Manythnkas for that.

Any quick comment on how would you improve performance ?

Regards
Bill
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

649 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