Есть один феномен, связанный с внутренней сортировкой в mySQL, которая служит для реализации функционала DISTINCT (выбора уникальных значений). Если вы пытаетесь сортировать по столбцу присоединенной таблицы, отсутствующему в результатах запроса, то сортировка ORDER BY не будет работать так, как вы ожидаете.
Рассмотрим пример. Здесь я пытаюсь отсортировать список публикаций (это база Drupal) по датам, указанным во внешней таблице с некоторым рейтингом. Рейтинг может быть проставлен многократно, потому я отбрасываю дубли по DISTINCT.
|
1 2 3 4 5 6 |
# пример № 1 SELECT DISTINCT node.nid FROM node INNER JOIN rating_log ON rating_log.nid = node.nid ORDER BY rating_log.dtm DESC LIMIT 0, 10 |
Теперь добавим в вывод столбец, по которому мы сортируем, и посмотрим какие отличия будут в полученных выборках.
|
1 2 3 4 5 6 |
# пример № 2 SELECT DISTINCT node.nid, rating_log.dtm FROM node INNER JOIN rating_log ON rating_log.nid = node.nid ORDER BY rating_log.dtm DESC LIMIT 0, 10 |
а результаты следующие:
 |
 |
| результат первого запроса |
результат второго запроса (со столбцом, по которому проводится сортировка) |
Первые три строки совпали, а дальше результаты расходятся.
Сортировка ORDER BY по столбцам основной таблицы (node) в запросе №2, будет производится как и ожидается — в порядке убывания дат. Значит в первом запросе этот порядок иной, не тот что мы ожидаем. Второй пример указывает нам на то, что нужно каким то образом «присоединить» нужные столбцы к основной таблице. Это можно сделать с помощью временной таблицы, не выходя за рамки одного предложения SELECT.
|
1 2 3 4 5 6 7 |
# пример № 3 SELECT DISTINCT node.nid FROM node JOIN (SELECT MAX(rating_log.dtm) last_dtm, nid FROM rating_log GROUP BY nid) AS rating ON rating.nid = node.nid ORDER BY rating.last_dtm DESC LIMIT 0, 10 |
Вместо inner join-а существующей таблицы, мы присоединяем временную. Сортировка производится уже по присоединенному столбцу last_dtm.
Результат последнего запроса:
От второго варианта отличается выдача последних трех строк. Они имеют одинаковое значение даты (dtm) — столбца, по которому производится сортировка.
Ещё варианты…
Я упомянул, что сортировка по столбцам основной таблицы проходит без проблем. Если структура данных позволяет, то можно добавить нужный для сортировки столбец в основную таблицу. Мы можем, к примеру, устанавливать последнюю дату выставления рейтинга (dtm) каждый раз при добавлении записей в rating_log, и помещать это значение в доп. столбец таблицы node.
В определенных случаях сортировку или отсеивание дублей можно производить на стороне PHP, тогда проблема тоже будет снята.