Как использовать функцию MAX в SQL для выбора максимальной зарплаты по департаментам и получить имена сотрудников с этой зарплатой?

Что проверяют

Понимаете ли вы, что MAX с GROUP BY теряет саму строку сотрудника, и умеете ли её вернуть. Почти всегда следом спрашивают, что будет, если максимальную зарплату в отделе получают двое, и как написать то же самое через LINQ в EF Core.

Как отвечать

Покажите два решения и объясните разницу. Классическое: подзапрос с агрегатом и соединение по паре «отдел, зарплата»:

SELECT e.Name, e.Salary, e.DepartmentId
FROM Employees e
JOIN (SELECT DepartmentId, MAX(Salary) AS MaxSalary
      FROM Employees GROUP BY DepartmentId) m
  ON m.DepartmentId = e.DepartmentId AND m.MaxSalary = e.Salary;

Современное: оконная функция, которая читает таблицу один раз:

SELECT Name, Salary, DepartmentId
FROM (SELECT Name, Salary, DepartmentId,
             RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS rnk
      FROM Employees) t
WHERE rnk = 1;

Что стоит проговорить:

• RANK() или DENSE_RANK(): вернут всех, у кого зарплата равна максимуму. ROW_NUMBER() оставит одного, причём случайного;
• NULL: MAX пропускает пустые значения, а отдел, где зарплата не заполнена ни у кого, из результата выпадет;
• индекс (DepartmentId, Salary): оба варианта берут максимум по индексу, не сортируя всю таблицу.

В EF Core надёжно транслируется коррелированный подзапрос: db.Employees.Where(e => e.Salary == db.Employees.Where(x => x.DepartmentId == e.DepartmentId).Max(x => x.Salary)). Оконную функцию проще выполнить через FromSql или Dapper.

Где спотыкаются

Пишут SELECT Name, MAX(Salary) ... GROUP BY DepartmentId. SQL Server и PostgreSQL вернут ошибку, а MySQL с выключенным ONLY_FULL_GROUP_BY отдаст имя случайного сотрудника отдела.

Берут ROW_NUMBER() и теряют второго сотрудника с той же зарплатой, не уточнив у интервьюера, нужен ли он.

Соединяют подзапрос только по зарплате, без отдела. Тогда сотрудник, чья зарплата совпала с максимумом чужого отдела, попадает в выдачу по ошибке.