Onur Yıldırım Onur Yıldırım - 4 months ago 10
MySQL Question

Same Query retruns different results (MySQL Group By)

This only happens for queries that force

GROUP BY
after
ORDER BY
.

Goal:



Get latest
balance
for each
unit
for the given
cardID
.

Table:



cardID | unit | balance | date
--------|-----------|-----------|--------------
A1 | DEPOSIT | 100 | 2016-05-01
A1 | DEPOSIT | 90 | 2016-05-02
A1 | DEPOSIT | 80 | 2016-05-03
A1 | DEPOSIT | 75 | 2016-05-04
A1 | MINUTE | 1000 | 2016-05-01
A1 | MINUTE | 900 | 2016-05-02
A1 | MINUTE | 800 | 2016-05-03


Query:



SELECT * FROM (
SELECT unit, balance
FROM cardBalances
WHERE cardID = 'A1'
ORDER BY date DESC
) AS cb
GROUP BY cb.unit;


Expected Result (MySQL v5.5.38):



unit | balance
---------|-----------
DEPOSIT | 75
MINUTE | 800


Unexpected Result (MySQL v5.7.13):



unit | balance
---------|-----------
DEPOSIT | 100
MINUTE | 1000


After upgrading to MySQL v5.7.13, the result returns the initial balances; as if no deduction occurred for the given card.

Is this a bug in MySQL version?

Would you suggest any other, more reliable way to solve this?

Answer

This is a bug in your use of the database. MySQL is quite explicit that when you include columns in the SELECT clause in an aggregation query -- and they are not in the GROUP BY -- then they come from indeterminate rows.

Such syntax is specific to MySQL. It is not only a bad idea to learn, but it simply normally not work in other databases.

You can do what you want in various ways. Here is one:

SELECT cb.*
FROM cardBalances cb
WHERE cardId = 'A1' AND
      cb.date = (SELECT MAX(date)
                 FROM cardBalances cb2
                 WHERE cb2.cardId = 'A1' AND cb2.unit = cb.unit
                );

This has the advantage that it can use an index on cardBalances(unit, CardId, date).