asked on

MySQL: Need to filter a query

I have two columns in my table...

ID (int)
Event
Email (VARCHAR(256))

I only want the result set to contain the most recent row for a given email address

ID Event Email
1 Opened mail1@test.com
2 Opened mail2222@test.com
3 Failed mail2222@test.com
4 Accepted mail4@test.com
5 Failed mail4@test.com
6 Failed mail5@test.com

I need a query which only show the most recent event for an email. For example:
1 Opened mail1@test.com
2 Opened mail2222@test.com
4 Accepted mail4@test.com

What query can I use?

1) If the most recent even for an email was Failed, ignore that email address
2) If a non-failing event is the most recent, ignore all prior events

ASKER CERTIFIED SOLUTION

mankowitz

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION

Julian Hansen

membership

This solution is only available to members.

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

Start Free Trial

curiouswebster

ASKER

thanks

mankowitz

@Julian-

Your code has an error. if you don't make a subquery, then the event column will not be correct . In your example, the row with ID=3 should have event = "failed", but you have "opened"

SOLUTION

Steve Marshall

membership

This solution is only available to members.

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

Start Free Trial

curiouswebster

ASKER

Yes, I saw that, but decided I may prefer a list of those which failed...

thanks

Steve Marshall

If you want only Failed events then change the subquery to = "Failed"

If you want the latest event with no exclusions then drop the AND T2.Event <> "Failed" from the subquery

Steve

curiouswebster

ASKER

mankowitz, I saw an error in your code (min vs max) which is why I glossed over his error and gave the JulianH the primary answer...

But you were first with the primary query.

Thanks for the help.