MySQL max(date) returns max date but other wrong fields

Question

I have a table for items purchased in different dates and would like to select only the latest date of each item. But when I use this code:

     SELECT `pid`,`price`, MAX(`date`) FROM `products_purchase` GROUP BY `pid`

id pid      price     date
1  6         2.50    2015-12-8
2  6         2.65    2015-11-23
3  6         3.11    2015-10-14

I have looked at many solutions for "mysql max(date) not working" but finally noticed that it select the right date but wrong row it shows the price from other row. For example it shows date as 2015-12-8 (which is max date) but price of 2.65 which is from other row. Please help.

http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html — Abhik Chakraborty, Jan 02 '16 at 18:07
It's because MySQL is a stupid database that lets you select a field that is not part of the grouping or part of the aggregation. It selects basically a random price per pid. — GolezTrol, Jan 02 '16 at 18:12

score 1 · Accepted Answer · answered Jan 02 '16 at 18:42

1

Get all the prices arranged by date and cut the first one

SELECT pid,
SUBSTRING_INDEX(GROUP_CONCAT(price ORDER BY DATE DESC),',',1)as price,
MAX(date)
FROM products_purchase 
GROUP BY pid

answered Jan 02 '16 at 18:42

Mihai

26,325
7
66
81

score 0 · Answer 2 · answered Jan 02 '16 at 18:37

0

SELECT pid,price,date FROM products_purchase GROUP BY pid order by date desc

answered Jan 02 '16 at 18:37

Dhruv Kapatel

873
3
14
27

This may work correctly in current versions of MySQL, but probably won't work in MariaDB (the open source fork of MySQL), nor is it guaranteed to work in future versions of MySQL. – Jan 02 '16 at 18:40
I don't know about mariaDB. why there is no guarantee to work in future mysql version? – Dhruv Kapatel Jan 02 '16 at 18:45
Because the behaviour is undocumented - you're trying to order by a field that is neither aggregated, nor grouped on, in a grouped query. I'm not even sure whether this version of the query will work in current versions of MySQL, though it may; I would normally put the `order by` in an inline view, then `group by` outside the inline view, but even that has the same issues I mentioned in my first comment. – Jan 02 '16 at 20:15

MySQL max(date) returns max date but other wrong fields

2 Answers2