Reputation: 469
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.
Upvotes: 0
Views: 2484
Reputation: 2242
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
Upvotes: 1