Victor Mezrin Victor Mezrin - 27 days ago 7
MySQL Question

GROUP_BY, MAX() and only_full_group_by

I have a table:

ID ACCOUNT BALANCE TIME
1 Bill 10 1478885000
2 Bill 10 1478885001
3 James 5 1478885002
4 Ann 20 1478885003
5 Ann 15 1478885004


I want to get latest (based on TIME) balance of several accounts. I.e.:

ACCOUNT BALANCE
Bill 10
Ann 15


I try to use this SQL:

SELECT ACCOUNT, BALANCE, max(TIME)
FROM T1
WHERE ACCOUNT IN ( 'Bill', 'Ann')
GROUP BY ACCOUNT


I receive error:

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'BALANCE' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by


I understand the error and tried different SQLs but still do not understand how to retrieve needed data without multiple queries.

P.S. I use MySQl 5.7

Answer
SELECT T1.ACCOUNT, T1.BALANCE, T1.TIME
FROM T1
JOIN (SELECT ACCOUNT, max(TIME) as m_time
      FROM T1
      WHERE T1.ACCOUNT IN ( 'Bill', 'Ann')
      GROUP BY ACCOUNT ) T2
  ON T1.ACCOUNT = T2.ACCOUNT 
 AND T1.TIME = T2.m_time
WHERE T1.ACCOUNT IN ( 'Bill', 'Ann')

EDIT: for multiple time change better use variables

SQL DEMO: I change the date of Ann to be the same

SELECT ACCOUNT, BALANCE, TIME
FROM (
      SELECT ACCOUNT, BALANCE, TIME, 
             @rn := if(ACCOUNT = @acc, @rn + 1 , 1) as rn,
             @acc := ACCOUNT
      FROM T1, (SELECT @rn := 0, @acc:= '') P
      WHERE ACCOUNT IN ( 'Bill', 'Ann')
      ORDER BY TIME desc, BALANCE desc
     ) T
WHERE T.rn = 1

OUTPUT

| ACCOUNT | BALANCE |       TIME |
|---------|---------|------------|
|    Bill |      10 | 1478885001 |
|     Ann |      20 | 1478885003 |