MAX: примеры (SQL)
MAX(expression any comparable): Same as expressionОсновные сведения о функции MAX
Функция MAX в MS SQL Server является агрегатной и предназначена для нахождения максимального значения в наборе данных. Она применяется в операторах SELECT, HAVING и ORDER BY, часто в сочетании с GROUP BY для анализа групп строк.
Синтаксис функции: MAX ( [ ALL | DISTINCT ] expression )
Параметры:
- ALL: применяется по умолчанию. Указывает, что функция должна учитывать все значения, включая дубликаты.
- DISTINCT: указывает, что учитываются только уникальные значения. Для числовых типов этот параметр редко влияет на результат, так как максимальное значение от фильтрации дублей не меняется.
- expression: выражение, обычно имя столбца, либо вычисляемое выражение на основе столбцов. Допустимыми являются типы данных, поддерживающие операции сравнения (числовые, символьные, дата/время).
Возвращаемое значение соответствует типу данных переданного выражения. Если набор строк пуст или все значения NULL, функция возвращает NULL. Функция игнорирует значения NULL при вычислении максимума, за исключением случая, когда все значения NULL.
Базовые примеры использования
Поиск максимальной цены товара:
SELECT MAX(Price) AS MaxPrice FROM Products;MaxPrice ---------- 263.50
Поиск максимальной длины названия товара:
SELECT MAX(LEN(ProductName)) AS MaxNameLength FROM Products;MaxNameLength -------------- 40
Использование с GROUP BY для поиска максимальной цены в каждой категории:
SELECT CategoryID, MAX(Price) AS MaxPrice FROM Products GROUP BY CategoryID;CategoryID MaxPrice ---------- ---------- 1 263.50 2 43.90 3 81.00
Использование с DISTINCT (результат обычно не отличается):
SELECT MAX(DISTINCT Price) AS MaxDistinctPrice FROM Products;MaxDistinctPrice ---------------- 263.50
Похожие функции в MS SQL
- MIN: находит минимальное значение в наборе. Применяется аналогично MAX.
- TOP...WITH TIES с ORDER BY: возвращает строки с максимальными значениями, включая дубли. Полезно, когда требуется получить не только значение, но и остальные данные строк.
- FIRST_VALUE с оконной функцией: при правильной сортировке и указании ROWS UNBOUNDED PRECEDING может имитировать поведение MAX в рамках окна.
- Применение подзапроса с ORDER BY и FETCH FIRST: альтернативный метод получения строки с максимальным значением.
Выбор функции зависит от задачи: MAX эффективна для получения одного агрегированного значения, а оконные функции или TOP полезны для получения связанных данных из строки с экстремальным значением.
Распространенные ошибки
Использование MAX с неподдерживаемыми типами данных, например, с XML или текстными типами больших размеров, вызывает ошибку.
SELECT MAX(CAST('text' AS TEXT)) FROM (VALUES (1)) AS t(c);Сообщение 8116, уровень 16, состояние 1 Аргумент 1 агрегатной функции MAX имеет недопустимый тип text.
Неправильное использование в предложении WHERE. Агрегатные функции не допускаются в WHERE, для фильтрации по агрегатным значениям используется HAVING.
SELECT CategoryID FROM Products WHERE MAX(Price) > 100 GROUP BY CategoryID;Сообщение 147, уровень 15, состояние 1 Агрегатная функция не допускается в предложении WHERE, если только она не находится в подзапросе, включенном в предложение HAVING или списке выборки.
Ожидание, что MAX вернет 0 для пустого набора. На самом деле возвращается NULL.
SELECT MAX(column) FROM table WHERE 1=0;(результат NULL, строки нет)
Изменения в новых версиях
В последних версиях MS SQL Server (начиная с 2012) для функции MAX были расширены возможности при использовании в качестве оконной функции с предложением OVER. Это позволяет вычислять максимальное значение в скользящем окне или секции без группировки всего набора.
Начиная с SQL Server 2017, улучшена обработка MAX в условиях выполнения с columnstore индексами, что повышает производительность агрегаций по большим таблицам.
Также, в актуальных версиях добавлена поддержка использования MAX с параметризованными порядками (в сочетании с OFFSET-FETCH), но это относится скорее к общему контексту запросов.
Расширенные примеры
Нахождение последней даты для каждого сотрудника с использованием MAX в коррелированном подзапросе:
SELECT e.EmployeeID, e.LastName,
(SELECT MAX(OrderDate) FROM Orders o WHERE o.EmployeeID = e.EmployeeID) AS LastOrderDate
FROM Employees e;EmployeeID LastName LastOrderDate ---------- --------- ----------------------- 1 Davolio 2023-05-15 00:00:00.000 2 Fuller 2023-05-18 00:00:00.000
Использование MAX как оконной функции для нахождения максимального значения в каждой секции (категории) с сохранением детализации строк:
SELECT ProductName, CategoryID, Price,
MAX(Price) OVER(PARTITION BY CategoryID) AS MaxPriceInCategory
FROM Products
ORDER BY CategoryID, Price DESC;ProductName CategoryID Price MaxPriceInCategory ------------- ---------- ------ ------------------- Product A 1 263.50 263.50 Product B 1 123.00 263.50 Product C 2 43.90 43.90
Поиск максимального значения среди вычисляемых выражений (разница между датами):
SELECT MAX(DATEDIFF(day, OrderDate, ShippedDate)) AS MaxDelayDays FROM Orders;MaxDelayDays ------------ 24
Использование MAX со строковыми значениями для нахождения последнего по алфавиту имени:
SELECT MAX(LastName) AS LastAlphabetically FROM Employees;LastAlphabetically ------------------ Zwilling
Комбинирование MAX и CASE для условного поиска максимума:
SELECT MAX(CASE WHEN CategoryID = 1 THEN Price ELSE NULL END) AS MaxPriceCategory1 FROM Products;MaxPriceCategory1 ----------------- 263.50
Аналоги функции в других СУБД
MySQL, PostgreSQL, SQLite: синтаксис MAX идентичен MS SQL. Пример для MySQL:
SELECT MAX(salary) FROM employees;MAX(salary) ----------- 12000
Oracle: также поддерживает MAX, но требует обязательного указания выражения. Дополнительно часто используется с KEEP (DENSE_RANK LAST) в аналитических функциях для получения других значений строки.
SELECT MAX(sal) FROM emp;MAX(SAL)
--------
5000Sybase ASE: поведение MAX аналогично MS SQL (из-за общих корней).
Ключевое отличие в некоторых СУБД (например, PostgreSQL) — возможность использовать MAX с фильтрацией по окну (OVER), что также поддерживается в MS SQL с версии 2005.