?
Solved

Get unique records in a timeframe

Posted on 2013-01-14
8
Medium Priority
?
221 Views
Last Modified: 2015-01-07
Hi all,

I need to get the unique records from a Mysql database within a timeframe. All these records have a field with an emailadres. I need all the records from the last week (say 2013-01-07 00:00:00 - 2013-01-14 00:00:00) but duplicate emailadresses with older dates should be excluded.

+----+---------------------+----------------+----------+
| id |      datetime       |     email      | somedata |
+----+---------------------+----------------+----------+
| 99 | 2013-01-13 00:00:00 | mail@mail.com  | adsf     |
| 98 | 2013-01-09 00:00:00 | thing@site.com | asdf     |
| 97 | 2013-01-02 00:00:00 | mail@mail.com  | asdf     |

Open in new window


using the timeframe  >= 2013-01-07 00:00:00 and <= 2013-01-14 00:00:00 would return us record 99 and 98 but there is an older record with a duplicate emailaddres. I need to skip these records, so the result would be record 98

How can this be done?

greetz,
walter
0
Comment
Question by:wal_toor
[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
8 Comments
 
LVL 10

Expert Comment

by:Damjan
ID: 38774288
Try with SQL SELECT DISTINCT Statement.
http://www.w3schools.com/sql/sql_distinct.asp
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 38774301
SELECT DISTINCT T.eMail
FROM yourTable as T
WHERE T.[DateTime] >= '2013-01-07'
AND T.[DateTime] < '2013-01-14'
AND T.eMail NOT IN (SELECT Distinct eMail FROM yourTable WHERE [DateTime] < '2013-01-07')

Not sure that they date syntax is correct for MySQL, but the concept is right.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 38777825
The WHERE clause would limit the date/time range.  The DISTINCT keyword will give you only one of each matching email address.

You might also consider a GROUP BY clause if you want to SELECT more columns than just the email address.
0
Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

 
LVL 8

Author Comment

by:wal_toor
ID: 38778337
Hi all,

thanks for your suggestions, I will play with them tomorrow.

greetz,
walter
0
 
LVL 2

Expert Comment

by:brijesh_chauhan
ID: 38818237
Hi,

Very easy solution for this problem.

select distinct email from table_name where date(column_name)>='2013-01-07' and date(column_name)<='2013-01-14';

This query will give you unique emails from 7th January to 14th January 2013.

Thanks,
0
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 2000 total points
ID: 38818305
@brijesh

That's good to get the OP the list of unique emails during that time period, but the OP said:

"using the timeframe  >= 2013-01-07 00:00:00 and <= 2013-01-14 00:00:00 would return us record 99 and 98 but there is an older record with a duplicate emailaddres. I need to skip these records, so the result would be record 98"

Note the part about "but there is an older record ... I need to skip these.."

another way to approach this might be:
SELECT T.eMail
FROM youTable as T
GROUP BY T.eMail
HAVING Sum(IIF(T.[DateTime] >= '2013-01-07' AND T.[DateTime] < '2013-01-14', 1, 0)) > 0
AND  Sum(IIF(T.[DateTime] < '2013-01-07', 1, 0)) = 0

Open in new window

This might be quicker than my earlier suggestion as you don't actually have to evaluate the IN clause, which can be slow.  I would test both methods!
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

762 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