Solved

Get unique records in a timeframe

Posted on 2013-01-14
8
207 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 (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
Technology Partners: 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 48

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The viewer will learn how to dynamically set the form action using jQuery.
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…

705 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