How to get the latest record for each value of a specific field?

Question

MySQL

There is a balances table:
account_id;
date;
balance;
I want to get the last entry for each account_id.
The following query throws an error:

SELECT account_id, max(date), balance
FROM account_balances GROUP BY account_id;

Error text:

[42000][1055] Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'core.account_balances.balance' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I understand that I am requesting a non-aggregated field, but how else would I get the last balance of each account?
Mysql version: 5.7.11 Default
settings. I didn't change anything after installation.

Answer 1

SELECT * FROM account_balances AS ab
WHERE date =
  (SELECT MAX(date) FROM account_balances AS ab2 WHERE ab.account_id = ab2.account_id) AND
ORDER BY ab.account_id

Answer 2

What is your table key? In my opinion, you have an architectural error here.
Let's assume that from id and date. Then there is a solution

CREATE TEMPORARY TABLE t1 (select account_id,MAX(date) as date from t2 GROUP BY account_id);

SELECT account_id,date,balance FROM table3 as t3
INNER JOIN t1 ON t1.account_id = t3.account_id AND t1.date = t3.date

Answer 3

you have select account_id, max(date), balance ..
and grouping only by account_id. (with such a grouping by one account_id, you need to apply some kind of agr function to balance, for example max(), then it will start, but the result will not be the same as you want =) )
do it like this:

select 
  ad.*, 
  ab.balance 
from
(
  select
    account_id, max(date) as max_date
  from tmp_account_balance
  group by account_id
) ad
inner join tmp_account_balance ab on ad.account_id = ab.account_id and ab.date = ad.max_date;

Answer 4

MySQL what? and what are the settings? there's a lot of stuff in 5.7...