Краткий ответ

Порядок строк в таблице и порядок, заданный внешним ORDER BY, не определяют порядок элементов внутри агрегата. Чтобы результат был детерминированным, сортировку нужно задать в самом агрегате специальным синтаксисом, поддерживаемым конкретной СУБД.

Например, в PostgreSQL порядок можно указать внутри string_agg:

SELECT
    department,
    string_agg(employee_name, ', ' ORDER BY hire_date, employee_id) AS employees
FROM employees
GROUP BY department;

Здесь сначала определяется порядок элементов для каждой группы, затем они объединяются в строку. Уникальный дополнительный ключ нужен для разрешения совпадений по основной сортировке.

Исторический контекст

Реляционная модель не задаёт естественного порядка строк: результат запроса представляет собой множество или набор строк без гарантированной последовательности. Агрегаты изначально в основном вычисляли числовые характеристики, но практические отчёты потребовали собирать значения группы в строку, массив или другой упорядоченный список.

Из этого возникла важная семантическая особенность: порядок элементов списка должен быть частью определения самого агрегирования, а не случайным свойством плана выполнения. Разные СУБД используют для этого различный синтаксис, поэтому переносимость такого запроса нужно проверять отдельно.

Постановка проблемы

Представим список товаров в заказе или сотрудников подразделения. Если порядок не задан внутри агрегата, сегодня элементы могут выглядеть отсортированными по дате вставки, а после изменения индекса, плана соединения, параллельного выполнения или версии СУБД — появиться в другой последовательности.

Сортировка во внешнем ORDER BY упорядочивает строки итогового результата, но не обязана упорядочивать строки, которые поступают во внутренний агрегат. Простая предварительная сортировка во вложенном запросе также не является надёжной заменой: оптимизатор может изменить порядок обработки, если он не значим для семантики операции.

Подробное решение

Нужно различать два уровня порядка:

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

В PostgreSQL внутренний порядок задаётся через ORDER BY внутри вызова агрегата. В других СУБД могут использоваться другие формы, например WITHIN GROUP или отдельные функции. Нельзя переносить синтаксис между диалектами без проверки документации.

Если сортировка не уникальна, результат всё ещё может быть недетерминированным среди строк с одинаковыми ключами. Поэтому обычно добавляют стабильный уникальный идентификатор: например, сортируют по дате, а затем по идентификатору записи.

Следует заранее определить правила для NULL, регистр, локаль и направление сортировки. Поведение агрегатов при NULL тоже зависит от функции и СУБД: например, в PostgreSQL string_agg пропускает NULL-значения, но не превращает их автоматически в текстовый маркер.

Главный компромисс — между переносимостью и выразительностью. Диалектный синтаксис обычно проще и эффективнее, а переносимый вариант может потребовать предварительной материализации или сборки списка на уровне приложения, что усложняет запрос и иногда увеличивает объём передаваемых данных.

Ситуация из практики

В отчёте по подразделениям имена сотрудников объединяли в одну строку. Сначала разработчик отсортировал итоговые подразделения по названию, рассчитывая, что имена внутри каждого списка также будут отсортированы. На тестовых данных это выглядело правильно, но после добавления индекса порядок сотрудников начал изменяться.

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

Выбрали третий вариант и отдельно зафиксировали правила обработки одинаковых дат и NULL. В результате отчёт стал воспроизводимым независимо от выбранного плана выполнения; при переносе на другую СУБД синтаксис агрегата пришлось адаптировать.

Что кандидаты часто упускают

Достаточно ли добавить внешний ORDER BY, чтобы элементы списка внутри каждой группы были упорядочены?

Нет. Внешний ORDER BY применяется к строкам, возвращённым после группировки, а список уже сформирован внутри агрегата. Он может расположить группы по названию, сумме или другому критерию, но не задаёт порядок значений, накопленных в каждой группе.

Почему сортировка внутри агрегата должна включать дополнительный уникальный ключ?

Если два элемента имеют одинаковое значение основного ключа сортировки, их взаимный порядок не определён. СУБД может вернуть их в любой последовательности, особенно при параллельном или индексном выполнении. Добавление уникального идентификатора превращает сортировку в детерминированную и делает результат стабильным между запусками.

Можно ли считать предварительно отсортированный вложенный запрос универсальным способом упорядочить значения агрегата?

Нет. Внешний запрос не обязан сохранять порядок строк из вложенного запроса, если этот порядок не является частью семантики операции на текущем уровне. Оптимизатор может убрать сортировку, изменить план соединения или распараллелить обработку. Надёжный способ — использовать механизм упорядочивания, предусмотренный самим агрегатом в конкретной СУБД.