What are the rules of GROUP BY, and what are ONLY_FULL_GROUP_BY and ANY_VALUE?
What are the rules of GROUP BY, and what are ONLY_FULL_GROUP_BY and ANY_VALUE?
In standard SQL, every column in the SELECT list of a grouped query must be either inside an aggregate function or listed in GROUP BY (or functionally dependent on a grouped column).
-- error in PostgreSQL, SQL Server, Oracle and MySQL 5.7+ with ONLY_FULL_GROUP_BY
SELECT department, name, MAX(salary)
FROM employees
GROUP BY department;
-- 'name' is neither grouped nor aggregated: which employee's name should be shown?
-- 1. group by it too (changes the grain)
SELECT department, name, MAX(salary) FROM employees GROUP BY department, name;
-- 2. aggregate it
SELECT department, MAX(name) AS some_name, MAX(salary) FROM employees GROUP BY department;
-- 3. you want the employee with the top salary: use a window function
SELECT department, name, salary
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.