CODEWITHSUNDEEP
sql
user965644
user965644

Reputation: 13

Eliminate duplicates within a same table

I want to get an email list from a table for specific dates.

Select email FROM clients
WHERE MONTH(created)=08
GROUP BY email

I would like to eliminate the email if it already existed before in the table. so if he was created before the month 08. (an email can be created multiple times because I work for a login site). Can anybody help me out.

Thank you

Upvotes: 1

Views: 86

Answers (2)

Andriy M
Andriy M

Reputation: 77677

You could first build a list of unique emails with their earliest created dates, then select from that list picking only those that were created in the month you want:

SELECT
  email
FROM (
  SELECT
    email,
    MIN(created) AS created
  FROM clients
  GROUP BY
    email
) s
WHERE MONTH(created)=08

Upvotes: 1

Marco
Marco

Reputation: 57573

This works in MySql:

DROP TEMPORARY TABLE IF EXISTS p;
CREATE TEMPORARY table p (email VARCHAR(50));
INSERT INTO p
   SELECT email FROM client 
   WHERE MONTH(created) = 8 
   GROUP BY email;
DELETE FROM client
WHERE email IN 
  (SELECT * FROM p)
AND MONTH(Created) < 8

Anyway, you could have problems with creation year...

EDITED: if you want to get only emails that weren't createed before month=8, try this:

DROP TEMPORARY TABLE IF EXISTS p;
CREATE TEMPORARY table p (email VARCHAR(50));
INSERT INTO p
   SELECT email FROM client 
   WHERE MONTH(created) < 8 
   GROUP BY email;
SELECT email FROM client
WHERE 
  MONTH(created)=8 AND
  email NOT IN (SELECT * FROM p)

Upvotes: 0

Related Questions