mySql: a faulty returned result

Question

I have this table:

product_id | name   |  status   |update_date
___________|________|___________|___________
   1       | prod1  | bought    | 2016-04-20
   2       | prod2  | bought    | 2016-04-20
   3       | prod3  | bought    | 2016-04-20
   1       | prod1  | sold      | 2016-04-22

I execute the following query:

select status, max(update_date), product_id from product group by product_id;

I get this result:

bought| 2016-04-22 12:25:00 |   1
bought| 2016-04-20 10:10:10 |   2
bought| 2016-04-20 10:10:10 |   3

I wonder why for the product with product_id = 1, I get the status bought and not sold!!

I wanna get the last status for each product.

You probably want `group by status,product_id` – Mihai Apr 25 '16 at 09:49 — Mihai, Apr 25 '16 at 09:49
for each product, I wanna get its last status. – senior Apr 25 '16 at 09:51 — senior, Apr 25 '16 at 09:51

juergen d · Answer 1 · 2016-04-25T10:14:05.620

3

Because if you select columns that are neither in the group clause nor be aggregated with functions like min(), max()... you get any value from the available ones in the group.

This feature of MySQL is confusing and that is why other DB engines return an error using this query because status is not aggregated.

edited Apr 25 '16 at 10:14

answered Apr 25 '16 at 09:52

juergen d

201,996
37
293
362

Nice and compact explanation. – 1000111 Apr 25 '16 at 10:08

score 2 · Accepted Answer · edited May 23 '17 at 12:23

2

Try this:

SELECT 
P.status,
P.update_date,
P.product_id
FROM product P
INNER JOIN 
(
select 
max(update_date) max_time, 
product_id 
from product 
group by product_id ) t
ON P.product_id = t.product_id AND P.update_date = t.max_time

It lies in this category : SELECT THE WHOLE ROW HAVING THE MAX VALUE.

Please look at this POST

edited May 23 '17 at 12:23

Community

1
1

answered Apr 25 '16 at 09:52

1000111

13,169
2
28
37

score 1 · Answer 3 · answered Apr 25 '16 at 10:20

1

try this

select product_id, status, max(update_date) from product where (product_id,update_date) IN (select product_id, max(update_date) from product group by product_id)group by product_id, status

answered Apr 25 '16 at 10:20

JYoThI

11,977
1
11
26

mySql: a faulty returned result

3 Answers3