Задача на сортировку

[blockdog]

Нужно найти предыдущую запись по дате, но если даты совпадают, тогда предыдущую запись нужно определять по ID.

Посмотрите пример, там две записи (2 и 4) с одинаковыми датами. Нужно сделать так, чтобы для 4 возвращалась 2, а для 2 возвращалась 1. Но при этом нужно, чтобы работала нормальная сортировка по дате, если записей с совпадающими датами нет.

Реально закручено всё, но на примере проще всего понять.

[soltpain]

жаль что в мускуле нет синтаксиса: SELECT * FROM test WHERE ID = 3 ORDER BY `Date` DESC, `ID` DESC LIMIT RELATIVE(-1,1)
иногда хочется иметь лимит относительно текущей записи… а так — два запроса туда-сюда отдельных с полной сортировкой, иначе оптимизация Limit + Where всё испортит

[blockdog]

Опишите отдельный запрос с «полной сортировкой».
Если я правильно понимаю, то вы хотите выбрать подзапросом все записи, подходящие под условия `date` <= $date AND `id` != $id, а основным запросом отсеять те, которые не подходят по условию совпадающих дат? Наверное, так тоже можно сделать, но тут всё-таки получится два запроса (точнее запрос с подзапросом), а это не самое лучшее решение — можно одним запросом однозначно определить.

[NeX]

4 будет предыдущей для 2

SELECT `id` FROM `test` WHERE `date` <= $date AND `id` < $id ORDER BY `date` DESC, `id` DESC LIMIT 1
чем не подходит?

[blockdog]

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

[NeX]

Сформулируйте тогда условие задачи нормально. Судя по всему, вас мало кто может понять сейчас

[blockdog]

Я очень долго бился над формулировкой задачи. Видимо, недостаточно.

Попробую другими словами: есть таблица с записями, нужно для каждой записи таблицы получить предыдущую запись по следующим условиям:
1) дата предыдущей записи меньше или равна текущей записи
2) если дата равна, тогда предыдущая запись определяется последовательностью ID, например, если для записей 123, 456, 789 даты равны, то для 456 предыдущей будет 123, и т.д.

В приведенной в задаче тестовой таблице правильная последовательность предыдущих ID от 5 будет следующая: 5, 3, 4, 2, 1

[tkf]

ээ ORDER BY 'date' DESC ,'id' DESC или я что то не так понял?

[tkf]

сорри, не увидел ниже habrahabr.ru/blogs/mysql/85945/#comment_2567913

[blockdog]

Наверное, не так поняли.
При совпадении дат `date` DESC откидывается, и сортируется по `id` DESC.
В данном случае будет возвращаться больший из последовательности ID, отличный от текущего. То есть, для 2 мы получим 4, а для 4 получим 2 — зациклились.

[tkf]

Такс наверно я туплю, но давайте посмотрим
Есть
2010-03-01 4
2010-03-01 2
2010-03-01 1
2010-02-28 3
в таком виде они станут после ORDER BY 'date' DESC ,'id' DESC
так что теперь надо будет сформировать условия выборки, как ниже написано WHERE `date` < $date or (`date` = $date and `id` < $id) и это вернет правильный результат.

[blockdog]

Откуда 2010-02-28?
Все даты: 2010-03-01, отличаются только часом.

[tkf]

это был пример
| 5 | 2010-03-01 14:00:00 | Test 5 |
| 3 | 2010-03-01 13:00:00 | Test 4 |
| 4 | 2010-03-01 12:00:00 | Test 3 |
| 2 | 2010-03-01 12:00:00 | Test 2 |
| 1 | 2010-03-01 11:00:00 | Test 1 |

Пусть будет вот так, сути это не изменит.

[blockdog]

| 4 | 2010-03-01 12:00:00 | Test 3 |
| 2 | 2010-03-01 12:00:00 | Test 2 |

SELECT `id` FROM `test` WHERE `date` <= '2010-03-01 12:00:00' AND `id` != 4 ORDER BY `date` DESC, `id` DESC LIMIT 1
Вернет: 2

SELECT `id` FROM `test` WHERE `date` <= '2010-03-01 12:00:00' AND `id` != 2 ORDER BY `date` DESC, `id` DESC LIMIT 1
Вернет: 4

Вот и закольцевалось…

Если вы хотите `date` <= $date AND `id` < $id сделать (как в первом комментарии этой ветки), тогда вы не сможете получить предыдущую запись по дате, у которой ID больше текущего.

В моей тестовой таблице этого случая для предыдущей записи нет, но если вы попробуете таким запросом найти следующую запись: `date` >= $date AND `id` > $id, тогда 3 никогда не выпадет (по дате она после 4, но id у нее не > 4).

WHERE `date` < $date or (`date` = $date and `id` < $id) остается правильным решением, учитывающим это.

[alexius2]

SELECT `id` FROM `test` WHERE `date` <= $date AND `id` != $id ORDER BY `date` DESC, (`id` < $id) DESC LIMIT 1

А так не пойдет разве?

[blockdog]

Нет. Такой запрос для 3 записи вернет 2, а должен вернуть 4.
Затем для 4 он вернет 2, а для 2 вернет 4 — зациклился…

[SabMakc]

SELECT `id` FROM `test` WHERE `date` < $date or (`date` = $date and `id` < $id) ORDER BY `date` DESC, `id` DESC LIMIT 1
Не оно?

[blockdog]

Да! Молодец!
У меня намного сложнее решение, но в принципе, подход примерно такой же.
Спасибо за решение, оно действительно изящное и работает!

[SabMakc]

Вообще-то полностью согласно Вашей формулировке (вернее, практически):
1) дата предыдущей записи меньше текущей записи
или
2) если дата равна, тогда предыдущая запись определяется последовательностью ID

[blockdog]

Да, тут я сам спалился, попытавшись расшифровать своё же сложное условие задачи (:

[NeX]

не забудьте индекс добавить по date, id

[blockdog]

CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`content` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `date` (`date`)
) ENGINE=MyISAM;

[NeX]

KEY `date` (`date`, `id `) может лучше сработать, но надо проверить

[blockdog]

Скорее нет, потому что в данном случае такой составной индекс замедлит условие `date` < $date, которое будет очень быстро работать при индексе KEY `date` (`date`) за счёт «отбрасывания» из индекса всего, что >= $date.

В данном конкретном примере лучше использовать два индекса. Но, спасибо вам за то, что акцентировали на этом внимание — это действительно важно!