Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Get unique records in a timeframe

Posted on 2013-01-14
8
Medium Priority
?
233 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
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…
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.
Suggested Courses

596 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