CODEWITHSUNDEEP
mysql
Thiatt
Thiatt

Reputation: 574

Select MAX(timestamp) GROUP BY

I have the following table:

+----+----------+---------+--------+-------+---------------------+
| id | order_id | user_id | status | notes | timestamp           |
+----+----------+---------+--------+-------+---------------------+
|  3 |        1 |       0 |      0 |       | 2015-11-29 22:49:44 |
|  4 |        1 |       0 |      2 |       | 2015-11-29 22:51:51 |
|  5 |        2 |       0 |      0 |       | 2015-11-29 23:14:26 |
|  6 |        3 |       0 |      0 |       | 2015-11-29 23:15:52 |
+----+----------+---------+--------+-------+---------------------+

Why is the following query:

SELECT
    order_id,
    `status`,
    MAX(timestamp)
FROM
    order_status
GROUP BY
    order_id

Returning the following result?

+----------+--------+---------------------+
| order_id | status | MAX(timestamp)      |
+----------+--------+---------------------+
|        1 |      0 | 2015-11-29 22:51:51 |
|        2 |      0 | 2015-11-29 23:14:26 |
|        3 |      0 | 2015-11-29 23:15:52 |
+----------+--------+---------------------+

Shouldn't line 1 have status = 2?

Upvotes: 1

Views: 13683

Answers (2)

Marc Wolf
Marc Wolf

Reputation: 14

This is very old but was my first result in google search when researching. You do not need a sub select, this is the correct way to solve this. 

SELECT
    DISTINCT (order_id),
    status,
    timestamp
FROM
    order_status
ORDER BY order_id, timestamp DESC

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

The fact that it returns anything at all is due to a quirk in MySQL. After all, what value should be returned for status? It is not included in the GROUP BY. So, MySQL returns a value from an indeterminate row. Most other databases would simply return an error.

Try something like:

select os.*
from order_status os
where timestamp = (select max(os2.time_stamp)
                   from order_status os2
                   where os2.order_id = os.order_id
                  );

Upvotes: 1

Related Questions