Retreive the last record for each group respecting some criteria

Question

I try to write an sql request to find the last record for each group, the is the layout of my table:

 --------------------------------------
| id | Group | message | Status | date|
 -------------------------------------
| 1  | A     | msg1    | sent   | dt  |
 -------------------------------------
| 2  | A     | msg2    |deferred| dt2 |
 -------------------------------------
| 3  | B     | msg3    |deferred| dt  |
 -------------------------------------
| 4  | B     | msg4    |deferred| dt  |
 -------------------------------------
| 5  | B     | msg5    |Bounced | dt  |
 -------------------------------------
| 6  | C     | msg6    |sent    | dt  |
 -------------------------------------
| 7  | D     | msg7    |deferred| dt  |
 -------------------------------------
| 8  | D     | msg8    |deferred| dt  |
 -------------------------------------
| 9  | D     | msg9    |sent    | dt  |
 -------------------------------------

And i try to get this output:

 --------------------------------------
| id | Group | message | Status | date|
 -------------------------------------
| 2  | A     | msg2    |deferred| dt  |
 -------------------------------------
| 5  | B     | msg5    |bounced | dt  |
 -------------------------------------
| 8  | D     | msg8    |deferred| dt  |
 -------------------------------------

that mean that i want, for each group, the last "deferred" or "bounced" message using the date that i have in my table. I start by doing this request, but i don't know how to modify it to get the result that i want:

SELECT ee.Group, ee.message
FROM email_errors ee
LEFT JOIN email_errors ee2 ON ( ee.Group = ee2.Group
AND ee.id < ee2.id ) 
WHERE ee2.id IS NULL 
AND ee2.status <>0

I note the ee.status <> 0 because in my real table i replaced the 3 possible status bu 0,1,2, and i replaced the sent status by 0.

Yes i see but here i am blocked because i have to choose the last record depending on the status that i have, and the date, and that's what i don't know how to write it — Ahmadhc, Nov 07 '13 at 15:14

score 1 · Accepted Answer · answered Nov 07 '13 at 15:35

I'd do the following:

SELECT ee.*
FROM email_errors as ee
CROSS JOIN
(
    SELECT ee.Group as grp, MAX(ee.id) AS max_id
    FROM email_errors as ee
    CROSS JOIN
    (
        SELECT Group as grp, MAX(date_t) AS max_date
          FROM email_errors
          WHERE status <> 0
          GROUP BY Group
    ) aux
    WHERE ee.Group = aux.grp
    AND ee.date_t = aux.max_date
    GROUP BY ee.Group
) outer_aux
WHERE ee.Group = outer_aux.grp
AND ee.id = outer_aux.max_id

score 1 · Answer 2 · answered Nov 07 '13 at 15:49

1

Note that group is a reserved word in MySQL - so not a good name for a column...

SELECT x.* 
  FROM email_errors x 
  JOIN 
     ( SELECT email_group
            , MAX(id) max_id 
         FROM email_errors 
        WHERE status IN ('deferred','bounced') 
        GROUP 
           BY email_group
     ) y 
    ON y.email_group = x.email_group 
   AND y.max_id = x.id;

answered Nov 07 '13 at 15:49

Strawberry

33,750
13
40
57

This query does not contain any clause on the date. @Ahmadhc: The cronology of the entries in your table is based on ID or on date? – Andrei Nicusan Nov 07 '13 at 16:10
No i use the date to determine my last Record. @AndreiNicusan your right. – Ahmadhc Nov 07 '13 at 16:21
In your example, your *dates* are all strings with the value 'dt'. I can't work with that. This query is the optimal solution. Amend it as required. – Strawberry Nov 07 '13 at 16:49

Retreive the last record for each group respecting some criteria

2 Answers2