Solved

Get unique records in a timeframe

Posted on 2013-01-14
8
199 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
8 Comments
 
LVL 10

Expert Comment

by:Damjan
Comment Utility
Try with SQL SELECT DISTINCT Statement.
http://www.w3schools.com/sql/sql_distinct.asp
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Author Comment

by:wal_toor
Comment Utility
Hi all,

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

greetz,
walter
0
 
LVL 2

Expert Comment

by:brijesh_chauhan
Comment Utility
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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 500 total points
Comment Utility
@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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 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

18 Experts available now in Live!

Get 1:1 Help Now