Easiest way to get all records after a specific row SQL

Question

I wrote a SQL query (below) that selects the next 25 records after the record with postID 201. (You don't have to read it)

SELECT
    title,
    content,
    table_with_rn.userID,
    table_with_rn.username,
    postID,
    post_timestamp
FROM
    (
    SELECT
        title,
        content,
        posts.userID,
        users.username,
        postID,
        post_timestamp,
        @rownum := @rownum + 1 AS row_number2
    FROM
        (
            posts
        INNER JOIN users ON posts.userID = users.userID
        )
    CROSS JOIN(
    SELECT
        @rownum := 0
    ) AS r
ORDER BY
    post_timestamp
DESC
) AS table_with_rn
WHERE
    row_number2 >(
    SELECT
        row_number
    FROM
        (
        SELECT
            postID,
            @rownum := @rownum + 1 AS row_number
        FROM
            (
                posts
            INNER JOIN users ON posts.userID = users.userID
            )
        CROSS JOIN(
        SELECT
            @rownum := 0
        ) AS r
    ORDER BY
        post_timestamp
    DESC
    ) AS twn
WHERE
    postID = 201
)
LIMIT 25

It sorts the table and then creates a column that holds the row number of each row. It then select the row number of the record with the specific postID, before selecting the records with greater row numbers from a duplicate table.

This query works fine, but it seems very complicated for a task that sounds rather simple. Is there a better/more efficient/simpler way of doing it?

Note: I realise I could skip the whole row_number thing and just use postID, since it is incremental, but I would like to keep my options open if I ever decide I don't want my pk to be an integer any more.

Note2: This is MySQL.

More of a MS SQL guy, but I believe your answer is here: https://stackoverflow.com/questions/3799193/mysql-data-best-way-to-implement-paging — Jacob H, Sep 29 '17 at 19:40
@JacobH Yes, that is exactly what I'm looking for. Create an answer, and I'll accept it. — Jans Rautenbach, Sep 29 '17 at 20:56

score 1 · Accepted Answer · answered Sep 29 '17 at 21:01

I am assuming that there is some column with which to determine whether a record is before or after the record with postID 201. From scanning your query, I'd say you have a timestamp column by which you want to order (to ignore the incremental nature of post ID).

If that is the case, one can employ a self join on the table some_table (for simplicity) where the timestamp columns of both table instance are compared. But one set of colums, is reduced to the timestamp of the record with postID = 201.

In other words, our join condition is 'all records of the table which have a timestamp larger than the one of the record with postID 201' which is the condition OP specified.

The result set now only contains records whose timestamp is larger than the one of postID 201 which we limit to only contain 25 entries. To get the ones directly after postID 201, we order by timestamp again.

The query could look like this:

SELECT 
  larger.*
FROM 
  some_table smaller
JOIN 
  some_table larger
  ON 
    smaller.timestamp < larger.timestamp
  AND smaller.postID = 201
ORDER BY larger.timestamp ASC
LIMIT 25

Easiest way to get all records after a specific row SQL

1 Answers1