Geek asks and answers

Getting the previous 10 results from a table closest to a specific date and supporting ascending sort order

I have a table containing calendar items; in my web application i have two views:

  • Preview 1: A basic view that shows the next 10 elements, from now on.
  • View 2: View showing the previous / next 10 items based on the timestamp of the first / last item in view 1. This is a troublemaker.

At the bottom of the page are the previous / next links that lead to view 2.

Problem:

How to get the previous set of 10 elements without knowing what the date is?

At first it seemed pretty simple to me, but apparently it is not.

Database Table:

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255)     | YES  | MUL | NULL    |                |
| start_time  | datetime         | YES  |     | NULL    |                |
| end_time    | datetime         | YES  |     | NULL    |                |
| created     | datetime         | NO   |     | NULL    |                |
| updated     | datetime         | YES  |     | NULL    |                |
| deleted     | tinyint(1)       | NO   |     | 0       |                |
+-------------+------------------+------+-----+---------+----------------+

SQL- 10 , ( ):

SELECT ci.*
FROM `calendar_item` AS `ci` 
WHERE ci.end_time >= NOW()
GROUP BY `ci`.`id` 
ORDER BY `ci`.`end_time` ASC
LIMIT 10

SQL- 10 1- :

SELECT ci.*
FROM `calendar_item` AS `ci` 
WHERE (ci.id IN (
    SELECT id FROM calendar_item 
    WHERE (end_time < FROM_UNIXTIME(1334667600))
    ORDER BY end_time DESC
))
GROUP BY `ci`.`id` 
ORDER BY `ci`.`end_time` ASC
LIMIT 10

URL-; 1 . , ; , timestamp. , , . , , , , :

SELECT ci.*
FROM `calendar_item` AS `ci` 
WHERE ci.end_time <= 1334667600
GROUP BY `ci`.`id` 
ORDER BY `ci`.`end_time` ASC
LIMIT 10

, , - , . .

+3
1

, LIMIT

SELECT ci.*
FROM `calendar_item` AS `ci` 
WHERE (ci.id IN (
    SELECT id FROM calendar_item 
    WHERE (end_time < FROM_UNIXTIME(1334667600))
    ORDER BY end_time DESC
    LIMIT 10
))
GROUP BY `ci`.`id` 
ORDER BY `ci`.`end_time` ASC
LIMIT 10

< FROM_UNIXTIMESTAMP. ASC 10, 10.

, 10 , FROM_UNIXTIME, .

( ) : , , .

SELECT i.*
FROM (
    SELECT ci.*
    FROM calendar_item AS ci
    WHERE ci.end_time < FROM_UNIXTIME(1334667600)
    ORDER BY ci.end_time DESC
    LIMIT 10
) AS i
ORDER BY i.`end_time` ASC
+1

More articles: