GROUP_BY, MAX() and only_full_group_by

Question

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

A simple way to solve non aggregated columns: https://stackoverflow.com/a/40578865/439171 — Italo Borssatto, Mar 07 '20 at 18:35

Juan Carlos Oropeza · Accepted Answer · 2016-11-11T19:07:40.840

2

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, 
                       if(@acc := ACCOUNT, 1, 1) as rn                 
      FROM T1, (SELECT @rn := 0, @acc:= '') P
      WHERE ACCOUNT IN ( 'Bill', 'Ann')
      ORDER BY ACCOUNT, TIME desc, BALANCE desc
     ) T
WHERE T.rn = 1

OUTPUT

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

edited Nov 11 '16 at 19:07

answered Nov 11 '16 at 18:00

Juan Carlos Oropeza

47,252
12
78
118

@Matt What about placing on both? That way the `JOIN` is faster because the derived table doesnt have index – Juan Carlos Oropeza Nov 11 '16 at 18:09
I had the same SQL, but the TIME is not unique in my table. Due to app`s logic I can have several rows with the same TIME for account. As I understand in this case I need one more wrapper SELECT with 'group_by(ACCOUNT)' and 'max(BALANCE)'. Is there a more elegant solution? – Victor Mezrin Nov 11 '16 at 18:09
@VictorMezrin if TIME can be duplicate too then I would use variables to create a row number. Or perhaps instead of time will ID be unique and always represent a later time? – Matt Nov 11 '16 at 18:11
2

If have multiple `TIME` what `BALANCE ` will you like? biggest, random, last id?? – Juan Carlos Oropeza Nov 11 '16 at 18:12
@JuanCarlosOropeza Biggest one. I understand how to modify your SQL to receive that value. Funny that we have no simple way for this task )) – Victor Mezrin Nov 11 '16 at 18:17
1

Yes, in sql server or postgresql you can use `ROW_NUMBER()` here you have to use variables or weird query. – Juan Carlos Oropeza Nov 11 '16 at 18:18
I didnt see an easy way to update my query to solve the tie. So check the version with variables – Juan Carlos Oropeza Nov 11 '16 at 18:34
oops just saw you updated with variables right after posting one myself :) do note that there is one differnece which is how we set the variable to compare previous account. I used to use your same method until Gordon Linoff showed me the doucmenation that says reading and assinging variable in same statement can be problematic. So the use of a nested if() to assign the previous account forces the order of evaluation.. here is a link on it http://stackoverflow.com/questions/39499031/pre-ordering-a-group-by-statement/39499240#comment66316719_39499240 – Matt Nov 11 '16 at 18:56
Thanks @Matt, Several times I ask in what case that kind of assignment will fail and never got a straight answer. I dont see any example there neither? but at least show a better argument than the usual `I read it on the manual`. – Juan Carlos Oropeza Nov 11 '16 at 19:05
1

Thanks again @Matt, I also knew this version before, but the problem was didnt stuck in my brain. But I think now will, you need a special event like this comment to crave the stones :) – Juan Carlos Oropeza Nov 11 '16 at 19:21

ScaisEdge · Answer 2 · 2016-11-11T18:35:59.110

0

You have column in your select that are not in group by

or you add all the column not in aggregated function

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

or you change the sql_mode using

 SET sql_mode = ''

or

  SELECT ACCOUNT, BALANCE, TIME
  FROM T1
  where id  In (
            select id from T1 where (account, time ) in (
                        select account, max(time) 
                        from t1  
                        WHERE ACCOUNT IN ( 'Bill', 'Ann') group by account))

edited Nov 11 '16 at 18:35

answered Nov 11 '16 at 17:57

ScaisEdge

131,976
10
91
107

I will receive multiple results for account – Victor Mezrin Nov 11 '16 at 18:02
@VictorMezrin . in mysql 5.7 the use of asimmetric group by (the column in select and not in group by) is change you can turn mysql to the previous behaviour setting the sql_mode to ''; like in my answer .. in your console or in you ide just perform SET sql_mode = ''; – ScaisEdge Nov 11 '16 at 18:05
I have also add an query for select the rows you need – ScaisEdge Nov 11 '16 at 18:09
`id (` should be `id = (` ? – Juan Carlos Oropeza Nov 11 '16 at 18:10
@JuanCarlosOropeza should be in .. thanks answer updated . – ScaisEdge Nov 11 '16 at 18:36

score 0 · Answer 3 · answered Nov 11 '16 at 17:59

The error is quite clear. If you want the latest balance for each account, here is one way:

select t1.*
from t1
where t1.time = (select max(tt1.time) from t1 tt1 where t1.account = tt1.account);

You can add the where in the outer query to filter for particular accounts.

GROUP_BY, MAX() and only_full_group_by

3 Answers3