Solved

Get unique records in a timeframe

Posted on 2013-01-14
8
205 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
ID: 38774288
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
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 110

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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 create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

685 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