MYSQL query to find all duplicated record with specified column groupings

Question

I have a table that holds all the emails sent for email campaigns:

ID | campaign_id | date_sent | date_delivered | email | status

Status holds delivery information, DELIVERED or UNDELIVERED. I want to find all email addresses that their status is UNDELIVERED for the last five campaigns.

For example, email@address.com used to be ok and received all campaigns, but now the email address does not exist and the last 5 campaigns status is UNDELIVERED.

So far I am doing:

SELECT *, count(id) as occ FROM message_details WHERE status='FAILED' GROUP BY email

But this is showing the total of undelivered for every address and not the last 5.

Maybe I don't understand you question completely but why not use LIMIT? — Dany, Apr 27 '16 at 09:29

Toby D · Answer 1 · 2016-04-27T09:23:28.727

This may be a bit tricky to read. Basically, what it is trying to do is to get top 5 message_details for each email then select those that have number of failed times equal to 5.

Haven't got time to test this code though.

SELECT *, count(thirdTable.id) as failTimes FROM
(SELECT rs.id, rs.Status, rs.email
    FROM (
        SELECT *, Rank() 
          over (Partition BY Section
                ORDER BY RankCriteria DESC ) AS Rank
        FROM message_details 
        ) rs WHERE Rank <= 5 ) as thirdTable
Where thirdTable.status = 'FAILED' GROUP BY thirdTable.email HAVING failTimes = 5

score 0 · Answer 2 · answered Apr 27 '16 at 09:49

0

Try this,

SELECT TOP 5 CampID,COUNT(id) Occ FROM dbo.message_details
WHERE Status    = 'FAILED'
GROUP BY CampID
ORDER BY CampID desc

answered Apr 27 '16 at 09:49

Sagar R

595
3
14

score 0 · Answer 3 · edited May 23 '17 at 12:23

This is very similar to Using LIMIT within GROUP BY to get N results per group?, I've tried some answer to that post. What you want to fetch is last 5 fail sent campaign of all emails, so it seems that your sql SELECT *, count(id) as occ FROM message_details WHERE status='FAILED' GROUP BY email can not make it actually.

Assume your data like this:

message_details
---
| ID | campaign_id | date_sent | date_delivered | email | status | 
| 1 | 1 | 2016-04-21 | null | aaa@aaa.aaa | FAILED | 
| 2 | 2 | 2016-04-22 | null | aaa@aaa.aaa | FAILED | 
| 3 | 3 | 2016-04-23 | null | aaa@aaa.aaa | FAILED | 
| 4 | 4 | 2016-04-24 | null | aaa@aaa.aaa | FAILED | 
| 5 | 5 | 2016-04-25 | null | aaa@aaa.aaa | FAILED | 
| 6 | 6 | 2016-04-26 | null | aaa@aaa.aaa | FAILED | 
| 7 | 1 | 2016-04-21 | null | ddd@aaa.aaa | FAILED | 
| 8 | 2 | 2016-04-22 | null | ddd@aaa.aaa | FAILED | 
| 9 | 3 | 2016-04-23 | null | ddd@aaa.aaa | FAILED | 
| 10 | 4 | 2016-04-24 | null | ddd@aaa.aaa | FAILED | 
| 11 | 5 | 2016-04-25 | null | ddd@aaa.aaa | FAILED | 
| 12 | 6 | 2016-04-26 | null | ddd@aaa.aaa | FAILED |

Then run this sql,

SELECT t1.email, t1.campaign_id
FROM message_details t1
LEFT JOIN message_details t2 ON t1.email = t2.email AND t1.date_sent <= t2.date_sent
WHERE t1.status='FAILED'
GROUP BY t1.email, t1.campaign_id, t1.date_sent
HAVING COUNT(*) <= 5

I got below result set,

| email | campaign_id | 
| aaa@aaa.aaa | 2 | 
| aaa@aaa.aaa | 3 | 
| aaa@aaa.aaa | 4 | 
| aaa@aaa.aaa | 5 | 
| aaa@aaa.aaa | 6 | 
| ddd@aaa.aaa | 2 | 
| ddd@aaa.aaa | 3 | 
| ddd@aaa.aaa | 4 | 
| ddd@aaa.aaa | 5 | 
| ddd@aaa.aaa | 6 |

I don't know if this works for you, but you can take a look on that link above. And if you've got some solution for your post, share it to me will be appreciated.

MYSQL query to find all duplicated record with specified column groupings

3 Answers3