CREATE TABLE employee (
uuid uuid DEFAULT (UUID()) PRIMARY KEY,
department varchar(64),
salary int
);
INSERT INTO employee(department, salary) VALUES
('SALES', 100),
('SALES', 120),
('MARKETING', 150),
('SALES', 99),
('MARKETING', 135);
SET @@sql_mode = CONCAT(@@sql_mode, ',ONLY_FULL_GROUP_BY');
SELECT @@sql_mode;
SELECT uuid, department, MAX(salary)
FROM employee
GROUP BY department
;
WITH ranked_salary AS (
SELECT
uuid,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) salary_rank
FROM employee
)
SELECT uuid, department, salary
FROM ranked_salary
WHERE salary_rank = 1;