Иерархия сортировки по дате в ORDER BY

Здравствуйте.

Есть таблица в базе с колонками dt (дата создания записи) типа datetime и updated_dt (on update CURRENT_TIMESTAMP).

При выборке записей их необходимо отсортировать так:

1) сначала идут записи за сегодняшний день с датой добавления (dt) по убыванию;
2) записи добавленные вчера и позднее соритруются по дате добавления и обновления по убыванию (т.е. мы например, можем обновить запись, и тогда она поднимается наверх, но всё равно они идут после объявлений, добавленных сегодня - см. условие 1) ).

Мой запрос выглядит так (комментарии оставил для ясности - оставляю запрос полностью, так как подозреваю, что просто не вижу в нём очевидного решения проблемы):

Код (Text):

1.  
2. SELECT * FROM ads_table
3. /* определённый тип (рубрика), актуальность объявления */
4. WHERE type=1 AND  ( DATE_ADD( publish_dt, INTERVAL public_interval MONTH ) > NOW() )  
5. /* is_spec - флаг для специальных объявлений, а вот дальше условие, которым я пытаюсь решить задачу,
6. т.е. добавлено сегодня, то используем дату добавления, если раньше, то берём большее из добавления-обновления */
7. ORDER BY is_spec DESC, IF( dt >= '2014-04-21 00:00:01',dt ,GREATEST(dt, updated_dt)) DESC, id DESC
8. LIMIT 25

Но всё равно объявления, обновлённые сегодня (но добавленные в прошлом), проскакивают наверх, рядом со свежими. Как это можно исправить?

Если я заменяю в ORDER BY

Код (Text):

1. IF( dt >= '2014-04-21 00:00:01',dt ,GREATEST(dt, updated_dt)) DESC

на

Код (Text):

1. dt DESC

то получаю, то что мне нужно, но тогда дата обновления перестаёт влиять на выборку.

 

Получился вот такой запрос

Код (Text):

1.  
2. (SELECT * FROM metr_ads
3. WHERE TYPE =1 AND (DATE_ADD( publish_dt, INTERVAL public_interval MONTH ) > NOW( ))
4. AND dt >=  '2014-04-21 00:00:01')
5.  
6. UNION
7.  
8. (SELECT * FROM metr_ads
9. WHERE TYPE =1 AND (DATE_ADD( publish_dt, INTERVAL public_interval MONTH ) > NOW( ))
10. AND dt <  '2014-04-21 00:00:01')
11.  
12. ORDER BY is_spec DESC , GREATEST( dt, updated_dt ) DESC , id DESC
13. LIMIT 25

Но это всё равно не то, так как не получается вынести LIMIT 25 в конец. Приходится выносить всё с ORDER BY, что по-моему нивелирует результат использования UNION.

 

Я не могу написать запрос, похожий на то, что вы написали потому, что в нём используется LIMIT и OFFSET для постраничной навигации. Т.е. я не могу указать точное количество записей для каждого из подзапросов. В этой связи я и пытался вынести LIMIT в его конец.

И в любом случае, второй подзапрос мне нужно сортировать по условию GREATEST( dt, updated_dt ) DESC, чтобы обновлённые объявления поднимались вверх.

 

В итоге у меня получается примерно такой запрос:

Код (Text):

1. SELECT o . * FROM (
2.     (SELECT *, DATE_FORMAT(DATE_ADD( publish_dt, INTERVAL public_interval MONTH ), '%d.%m.%Y') as public_end_dt,
3.     (DATE_ADD( publish_dt, INTERVAL public_interval MONTH ) <= NOW()) as is_expired
4.     FROM my_table WHERE type=1 AND dt >=  '2014-04-22 00:00:01'
5.     ORDER BY is_spec DESC , dt DESC , id DESC)
6.  
7.     UNION ALL
8.  
9.     (SELECT *, DATE_FORMAT(DATE_ADD( publish_dt, INTERVAL public_interval MONTH ), '%d.%m.%Y') as public_end_dt,
10.     (DATE_ADD( publish_dt, INTERVAL public_interval MONTH ) <= NOW()) as is_expired
11.     FROM my_table WHERE type=1 AND ( DATE_ADD( publish_dt, INTERVAL public_interval MONTH ) > NOW() ) AND dt <  '2014-04-22 00:00:01'
12.     ORDER BY is_spec DESC , GREATEST( dt, updated_dt ) DESC , id DESC)
13.  
14. ) AS o LIMIT 25

Если я выполняю первый селект из вложенных, то получаю, то что мне надо. Выполняю второй - тоже прекрасный результат. Но вот когда выполняю весь запрос в целом, то первая часть (где все сегодняшние объявления) идёт ОК, а вот во второй полная каша. Т.е. в ней записи не отсортированы по этому условию ORDER BY is_spec DESC , GREATEST( dt, updated_dt ) DESC , id DESC

В Википедии по поводу UNION написано:

Вот тут-то и проблема: мне нужно, чтобы сегодняшние записи всегда были сверху, а если я буду использовать общий ORDER BY, тогда обновлённые записи из второго запроса будут вылезать наверх.