Sorting task

Perhaps, this task will seem trivial to someone, but personally I spent several hours on it, having spent the prompts “hall opinion” and “call a friend”. Why did I decide this? The answer is simple: I really needed to implement such an approach for my small site, Odio.ru. In short, there are published records from a variety of sites, contracted by RSS. The difficulty is that the dates in these records can completely coincide (even within one tape), while the sequence ID makes sense only within one tape, but does not affect the entire stream of records. So let's go to the conditions of the problem.

Since this is a blog about MySQL, I will SHOW CREATE TABLE for our test table:

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;

Now fill it with test data:
')
INSERT INTO `test` (`id` ,`date` ,`content`)
VALUES (NULL , '2010-03-01 11:00:00', 'Test 1'),
(NULL , '2010-03-01 12:00:00', 'Test 2'),
(NULL , '2010-03-01 13:00:00', 'Test 4'),
(NULL , '2010-03-01 12:00:00', 'Test 3'),
(NULL , '2010-03-01 14:00:00', 'Test 5');

As a result, we get the following table ( SELECT * FROM `test` ORDER BY `id` ):

| 1 | 2010-03-01 11:00:00 | Test 1 |
| 2 | 2010-03-01 12:00:00 | Test 2 |
| 3 | 2010-03-01 13:00:00 | Test 4 |
| 4 | 2010-03-01 12:00:00 | Test 3 |
| 5 | 2010-03-01 14:00:00 | Test 5 |

As you can see, the table is now sorted by ID and the dates are in the wrong order.

Now, actually, the task itself: we have one of the entries at the entrance (that is, we know the ID and DATE), we need to get the ID of the neighboring records (previous and next), while if the DATE is the same, then the previous record will have an ID less than the current and the next one has more.

The observant reader will immediately understand that if you just do (for the previous record): SELECT `id` FROM `test` WHERE `date` <= $date AND `id` != $id ORDER BY `date` DESC, `id` DESC LIMIT 1 , then we will "loop" between records 2 and 4, because they have the same DATE - that is, 2 will have a previous one for 4, and 4 will have a previous one for 2. At the same time, it is important that extreme records (1 and 5) as neighbors (respectively, the previous one for 1 and the next one for 5) did not return.

To simplify, let's look only for the previous entry. We start with 5 entries, we have ID = 5 and DATE = '2010-03-01 14:00:00'. We need to get record 3, and then we get for the conditions ID = 3 and DATE = '2010-03-01 13:00:00', and so on ...

I will clarify right away, the task has at least one solution;) One request - one previous entry, the same query with different parameters - the next previous entry. That is, the option "get everything and walk on it" does not fit. Also, the option “add a column ORDER_NUM and rebuild it for the whole table when adding a new record” is not suitable. The option “write IDs of already shown records and exclude them from the sample” is no longer suitable.

In general, we need an “honest” request, which, by the ID and DATE of the current record, will return the real ID of this previous record.

In order to avoid unnecessary flames in the comments, I answer immediately: yes, I deliberately hung the link to the address of my site, because it is a living example of the application of the solution to this problem. Well, of course, like any other site, he wants to come to him more often;)

Also, I reserve the right to use your request if it turns out to be better than what I came up with;) Naturally, with your permission ...

If in the conditions of the task something is not clear, I will be happy to clarify in the comments.

And please do not need to write: “What kind of nonsense? Use% framework_name%, it will do everything for you ... ”- I'm not interested, and the blog about MySQL, the task of sorting in MySQL, so we do everything within MySQL.

UPDATE Found 1 solution:

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

Submitted by : SabMakc

There are other solutions, perhaps more complicated than the one presented, but, nevertheless, you can continue the search ...

UPDATE 2 I cite a “complex” three-story solution to this problem:

SELECT `id`, `date`, IF (`date` = $date AND id < $id, 0, 1) AS `ordr`
FROM `test`
WHERE `date` <= $date AND `id` != $id
HAVING `date` < IF (`ordr` = 1, $date, NOW())
ORDER BY `ordr`, `date` DESC, `id` DESC
LIMIT 1

This is my decision. In essence, it does the same thing as the previous SabMakc solution, but through a slightly different gate ...