Как в SQL-запросе выбрать записи с максимальным значением в группе

При работе с прямыми SQL-запросами в среде 1С (особенно это актуально для платформы 1С v7.7) программисты часто сталкиваются с задачей получения «последнего» или «актуального» значения из таблицы. Типичный пример: у нас есть таблица с движениями МПЗ, ценами и позицией документа, и нам нужно вытащить для каждого товара только ту цену, которая соответствует его максимальной позиции в базе данных.

В этой статье мы подробно разберем, почему простая группировка не решает задачу, и изучим несколько эффективных способов получения нужных данных, включая классические JOIN-соединения и современные оконные функции.

Почему обычный GROUP BY не подходит для решения задачи

Давайте проанализируем ситуацию, с которой столкнулся автор темы. Если мы попытаемся использовать простой запрос с группировкой, например:

SELECT
  МПЗ,
  Цена,
  MAX(ПозицияДокумента)
FROM
  table
GROUP BY
  МПЗ, Цена

Мы столкнемся с проблемой: если для одного и того же МПЗ в таблице зафиксировано несколько разных цен, то SQL сгруппирует данные по уникальным парам «Товар + Цена». В итоге мы получим несколько строк для одного товара — по одной строке для каждой уникальной цены, что противоречит нашей задаче. Нам же нужно получить строго одну строку на каждый МПЗ.

Если же мы попробуем убрать Цена из блока GROUP BY, большинство современных СУБД (например, MS SQL Server) выдадут ошибку, так как колонка Цена не входит ни в агрегатную функцию, ни в условие группировки. Именно поэтому нам нужны более сложные конструкции.

Решение 1: Использование вложенного запроса и INNER JOIN

Это классический и наиболее универсальный метод, который будет работать практически в любой версии SQL Server. Давайте разберем его по шагам. Суть метода заключается в том, что сначала мы находим максимальную позицию для каждого товара, а затем соединяем этот результат с исходной таблицей, чтобы «подтянуть» соответствующие цены.

Рассмотрим структуру запроса подробнее:

SELECT
  mainTable.МПЗ,
  mainTable.Цена,
  mainTable.ПозицияДокумента
FROM
  table AS mainTable
INNER JOIN 
  (
    SELECT 
      МПЗ, 
      MAX(ПозицияДокумента) AS MaxPos
    FROM 
      table 
    GROUP BY 
      МПЗ
  ) AS subQuery
ON 
  mainTable.МПЗ = subQuery.МПЗ 
  AND mainTable.ПозицияДокумента = subQuery.MaxPos

Что происходит в этом запросе:

1. В подзапросе subQuery мы создаем временный набор данных, где для каждого уникального МПЗ вычисляется только одно значение — максимальная ПозицияДокумента.
2. Затем с помощью INNER JOIN мы сопоставляем исходную таблицу mainTable с результатами подзапроса.
3. Условие соединения ON гарантирует, что из основной таблицы будут выбраны только те строки, у которых и товар, и позиция совпадают с нашими максимальными значениями.

Этот метод надежен, но стоит помнить: если в одной и той же позиции документа для одного товара каким-то образом окажутся две разные цены (дубли), запрос вернет обе строки. Для исключения такой ситуации можно добавить дополнительную группировку или использовать DISTINCT в итоговой выборке.

Решение 2: Современный подход через оконные функции (ROW_NUMBER)

Если вы работаете с MS SQL Server 2005 и выше или используете современные версии PostgreSQL, мы крайне рекомендуем использовать оконные функции. Этот метод зачастую эффективнее, так как позволяет СУБД выполнить меньше операций сканирования таблицы.

Проанализируем пример кода с использованием ROW_NUMBER():

SELECT
  МПЗ,
  Цена,
  ПозицияДокумента
FROM (
  SELECT
    МПЗ,
    Цена,
    ПозицияДокумента,
    ROW_NUMBER() OVER (PARTITION BY МПЗ ORDER BY ПозицияДокумента DESC) AS RowNum
  FROM
    table
) AS RankedTable
WHERE
  RowNum = 1

Как это работает:

1. Функция ROW_NUMBER() нумерует строки.
2. Инструкция PARTITION BY МПЗ говорит системе: «Начинай нумерацию заново для каждого нового товара».
3. Инструкция ORDER BY ПозицияДокумента DESC определяет порядок нумерации — от самой большой позиции к самой маленькой. Таким образом, самая свежая запись всегда получает номер 1.
4. Во внешнем запросе мы просто отфильтровываем все записи, кроме тех, что получили RowNum = 1.

Этот способ более читаем и позволяет легко изменить логику. Например, если вам нужно получить не одну последнюю цену, а три последних, достаточно изменить условие на WHERE RowNum <= 3.

Решение 3: Оптимизация через CROSS APPLY

Для высоконагруженных баз данных 1С на MS SQL Server отличным вариантом станет оператор CROSS APPLY. Он работает по принципу «выполни подзапрос для каждой строки из левой таблицы».

Давайте посмотрим на пример:

SELECT
  distinctTable.МПЗ,
  lastPrice.Цена,
  lastPrice.ПозицияДокумента
FROM
  (SELECT DISTINCT МПЗ FROM table) AS distinctTable
CROSS APPLY
  (
    SELECT TOP 1 
      Цена, 
      ПозицияДокумента
    FROM 
      table AS t2
    WHERE 
      t2.МПЗ = distinctTable.МПЗ
    ORDER BY 
      t2.ПозицияДокумента DESC
  ) AS lastPrice

Этот метод особенно эффективен, если по полю МПЗ создан индекс. SQL Server будет использовать быстрый поиск (Index Seek) для каждого уникального товара, что значительно ускорит получение результата на огромных таблицах проводок или движений регистров.

Важные нюансы и производительность

Проанализируем несколько критических моментов, которые могут повлиять на работу вашего запроса:

1. Индексация: Независимо от выбранного способа, для быстрой работы запроса крайне желательно наличие составного индекса [МПЗ] + [ПозицияДокумента]. Без него сервер будет вынужден просматривать всю таблицу целиком (Table Scan), что создаст нагрузку и блокировки.
2. Обработка дублей (Ties): Если вы используете MS SQL Server и вам нужно получить все строки с максимальным значением (если их вдруг несколько), обратите внимание на конструкцию TOP 1 WITH TIES в сочетании с сортировкой.
3. Диалекты SQL: Как верно подмечено в обсуждении, некоторые базы данных (например, SQLite) ведут себя нестандартно и позволяют возвращать значения колонок, не указанных в GROUP BY, автоматически выбирая их из строки с агрегатом. Однако в профессиональной разработке на 1С и MS SQL полагаться на такое поведение нельзя — всегда пишите синтаксически корректные запросы.
4. Использование CTE: Если ваш исходный набор данных — это не просто таблица, а результат сложного запроса (как упомянул автор темы в сообщении №8), оформите его через WITH (Common Table Expressions). Это сделает код чище и избавит от необходимости создавать временные таблицы вручную.

Рассмотрим, как это выглядит структурно:

WITH PreparedData AS (
  -- Здесь ваш сложный исходный запрос
  SELECT МПЗ, Цена, ПозицияДокумента FROM SomeTables...
)
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY МПЗ ORDER BY ПозицияДокумента DESC) as num
  FROM PreparedData
) t WHERE num = 1

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