Как правильно использовать ORDER BY и GROUP BY в SQL?

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

Понимаете ли вы логический порядок выполнения запроса, а не только синтаксис. Тестировщику это нужно, чтобы сверять отчёты и выгрузки с базой и писать проверочные запросы без ложных срабатываний. Следом обычно спрашивают разницу между WHERE и HAVING.

Как отвечать

Начните с порядка: FROM и JOIN, затем WHERE, GROUP BY, HAVING, SELECT, ORDER BY и в конце LIMIT. Из него следует всё остальное:

• GROUP BY: схлопывает строки с одинаковыми значениями в одну. В SELECT остаются колонки группировки и агрегаты COUNT, SUM, AVG, MIN, MAX. PostgreSQL дополнительно пускает колонки, зависящие от первичного ключа, по которому группируют;
• WHERE и HAVING: WHERE фильтрует строки до группировки, агрегаты в нём писать нельзя. HAVING фильтрует готовые группы: HAVING COUNT(*) > 1;
• ORDER BY: выполняется после SELECT, поэтому в нём можно сослаться на псевдоним ORDER BY cnt DESC, а в WHERE нельзя;
• NULL: при группировке все NULL попадают в одну группу. При сортировке их место зависит от СУБД: в PostgreSQL при ASC они в конце, в MySQL в начале. Управлять можно через NULLS FIRST и NULLS LAST там, где этот синтаксис есть.

Практический пример для QA, поиск дублей после импорта:

SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1 ORDER BY cnt DESC;

Тут же разница COUNT(*) и COUNT(column): второй не считает строки с NULL в этой колонке, и в отчёте это легко принять за баг или пропустить настоящий.

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

Полагаются на порядок строк без ORDER BY. Стандарт его не гарантирует, GROUP BY тоже не сортирует (MySQL 8.0 убрал неявную сортировку), и тест, сверяющий первую строку, начинает мигать после смены плана запроса.

Добавляют в SELECT колонку, которой нет в GROUP BY. PostgreSQL выдаст ошибку, а MySQL с выключенным ONLY_FULL_GROUP_BY вернёт значение из произвольной строки группы.

Пишут агрегат в WHERE, например WHERE COUNT(*) > 1, и не понимают, почему запрос не выполняется. Условие на агрегат живёт в HAVING.