asked on

Get unique records in a timeframe

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

Damjan

Try with SQL SELECT DISTINCT Statement.
http://www.w3schools.com/sql/sql_distinct.asp

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

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.

wal_toor

ASKER

Hi all,

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

greetz,
walter

brijesh_chauhan

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,

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial