Get last item in each group MySql

Question

In the first table I have orders. In the second - messages which send to these orders. I need to get date and status from the last message (or NULL if messages didn`t send yet) for each orders where has active=1. The tables connected by composite key - "order_id + offer".

"orders" table:

+----------+----------+--------+----------+
| order_id | offer    | active | timezone |
+----------+----------+--------+----------+
|        6 | kopiya   |      1 |        0 |
|        6 | kopiya-3 |      1 |        0 |
|       10 | kopiya   |      1 |      180 |
|       23 | kopiya-2 |      1 |        0 |
|       27 | kopiya-2 |      0 |        0 |
+----------+----------+--------+----------+

"sms" table:

+------+----------+----------+------+--------+---------------------+
| key_ | order_id | offer    | type | status | date                |
+------+----------+----------+------+--------+---------------------+
| 1    |        6 | kopiya   | text |      1 | 2016-06-20 00:00:00 |
| 2    |        6 | kopiya-3 | text |      0 | 2016-06-21 00:00:00 |
| 3    |       10 | kopiya   | text |      0 | 2016-06-27 00:00:00 |
| 4    |       27 | kopiya-2 | text |      1 | 2016-06-21 00:00:00 |
| 6    |        6 | kopiya-3 | text |      1 | 2016-06-23 00:00:00 |
+------+----------+----------+------+--------+---------------------+

The result will be:

+----------+----------+---------------------+--------+
| order_id | offer    | last_date           | status |
+----------+----------+---------------------+--------+
|        6 | kopiya   | 2016-06-20 00:00:00 |      1 |
|        6 | kopiya-3 | 2016-06-23 00:00:00 |      1 |
|       10 | kopiya   | 2016-06-27 00:00:00 |      0 |
|       23 | kopiya-2 | NULL                |   NULL |
+----------+----------+---------------------+--------+

This query works not correctly:

SELECT o.order_id, o.offer, max(date) as last_date, status
FROM orders AS o
LEFT JOIN sms AS s
ON o.order_id=s.order_id AND o.offer=s.offer
WHERE `active` = 1
GROUP BY o.order_id, o.offer;

It show:

+----------+----------+---------------------+--------+
| order_id | offer    | last_date           | status |
+----------+----------+---------------------+--------+
|        6 | kopiya   | 2016-06-20 00:00:00 |      1 |
|        6 | kopiya-3 | 2016-06-23 00:00:00 |      0 |
|       10 | kopiya   | 2016-06-27 00:00:00 |      0 |
|       23 | kopiya-2 | NULL                |   NULL |
+----------+----------+---------------------+--------+

For key "6 kopiya-3" it return status=0 but expected 1, because it get this value from the first row instead row with max date. How can I fix this?

Answer 1

Is this just the same as Unix One's answer, just formatted differently? If so, then they beat me to it - but note that this assumes a PK on (order_id,offer), and returns a slightly different result set from what was requested.

SELECT o.order_id
     , o.offer
     , s.date last_date
     , s.status
  FROM orders o 
  LEFT
  JOIN 
     ( SELECT x.* 
         FROM sms x
         JOIN 
            ( SELECT order_id
                   , offer
                   , MAX(date) date 
                FROM sms 
               GROUP 
                  BY order_id
                   , offer
            ) y 
           ON y.order_id = x.order_id 
          AND y.offer = x.offer
          AND y.date = x.date
      ) s
     ON s.order_id = o.order_id 
    AND s.offer = o.offer 
  WHERE o.active = 1;

Answer 2

I think this does what you want:

select sms.*
from orders o left join
     sms
     on o.order_id = sms.order_id and o.offer = sms.offer
where sms.date = (select max(sms2.date)
                  from sms sms2
                  where sms2.order_id = sms.order_id and sms2.offer = sms.offer
                 ) or
      sms.order_id is null

For this query, I would advise an index on sms(order_id, offer, date).

Answer 3

Bingo, the problem is where you put the GROUP BY:

SELECT o.order_id, o.offer, max(date) as last_date, status
FROM orders AS o
LEFT JOIN sms AS s
ON o.order_id=s.order_id AND o.offer=s.offer
WHERE `active` = 1
GROUP BY o.order_id, o.offer;

This results in the following:

WITH C AS (SELECT --columns
FROM Orders AS A
WHERE O.Active = 1
GROUP BY O.Order_ID, O.Offer)

SELECT --columns
FROM C
INNER JOIN sms AS S ON O.Order_ID = S.order_ID
                   AND O.Offer = S.Offer;

Since the group by is not deterministic, you get the error you had. Instead, Try something like this:

SELECT B.ORDER_ID, A.OFFER, B.Status, B.DATE
FROM Orders A
INNER JOIN (SELECT Order_ID, Offer, MAX([DATE]) AS [DATE], MAX(Status) AS [Status]
            FROM #EXAMPLE2
            GROUP BY Order_ID, OFFER) B ON A.Offer = B.OFFER 
                                   AND A.Order_ID = B.Order_ID
WHERE ACTIVE = 1;

Results:
-- 6    kopiya      1       2016-06-20 00:00:00.000
-- 27   kopiya-2    1       2016-06-21 00:00:00.000
-- 6    kopiya-3    1       2016-06-23 00:00:00.000
-- 23   kopiya-2    NULL    NULL

Now the predicate on your Orders table does what it is supposed to do, and filters the results.

Answer 4

SELECT
  o.order_id,
  o.offer,
  s3.`date` AS last_date,
  s3.status
FROM
  orders o
  LEFT JOIN
    (SELECT
       s1.order_id,
       s1.offer
     FROM
       sms s1
       JOIN
         (SELECT
            MAX(key_) AS key_
          FROM
            sms
          GROUP BY
            order_id,
            offer) s2 ON s1.key_ = s2.key_) s3 ON (o.order_id = s3.order_id AND o.offer = s3.offer)

Answer 5

SELECT o.order_id, o.offer, max(date) as last_date, (select (SELECT sd.status FROM sms sd WHERE sd.order_id=o.order_id AND sd.offer=s.offer ORDER BY sd.key_ DESC LIMIT 1) as 'st' case when st = '1' then '1' else '0' end) as status
FROM orders AS o
LEFT JOIN sms AS s
ON o.order_id=s.order_id AND o.offer=s.offer
WHERE `active` = 1
GROUP BY o.order_id, o.offer;

that might work if my syntax is correct

side note: my answer is terrible lol