0
SELECT ENAME, HIREDATE, SAL 
FROM emp
WHERE (YEAR(HIREDATE), SAL) IN (
  SELECT YEAR(HIREDATE), MAX(SAL)
  FROM emp
  GROUP BY YEAR(HIREDATE)
);

error message

: SELECT ENAME, HIREDATE, SAL FROM emp WHERE (YEAR(HIREDATE), SAL) IN ( SELECT YEAR(HIREDATE), MAX(SAL) FROM emp GROUP BY YEAR(HIREDATE) ) LIMIT 0, 1000 Error Code: 1055. Expression #1 of HAVING clause is not in GROUP BY clause and contains nonaggregated column 'scott.emp.HIREDATE' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.000 sec

-- ok

SELECT YEAR(HIREDATE), MAX(SAL)
FROM emp
GROUP BY YEAR(HIREDATE)
yea(hiredate) max(sal)
1980 800
1981 5000
1987 3000
1982 1300
Bas H
  • 2,114
  • 10
  • 14
  • 23
mycatgib
  • 23
  • 4
  • If you are are looking for an explanation of functional dependency and how mysql implements it start here https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html – P.Salmon Apr 05 '23 at 06:33
  • But I guess you are wondering why running the sub query on it's own produces correct result? I suspect this is a bug and you should rewrite as a join. https://dbfiddle.uk/7cpJ7U7g – P.Salmon Apr 05 '23 at 06:52
  • [Error related to only_full_group_by when executing a query in MySql](https://stackoverflow.com/q/34115174/3404097) – philipxy Apr 05 '23 at 07:15

0 Answers0