patternsqlMinor

Select rows after a specific ID

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

afterselectrowsspecific

Problem

I have the following query, it chooses from a news articles table. Due to multiple sorting factors, I cannot locate the next page of date.

The parameter I have is the last ID in the query result set, so how can I fetch the next page... Again, I don't have a page index, all I have is the last ID from the previous query result set

SELECT        TOP (20) id, DATEDIFF(SECOND, CONVERT(DATETIME, '1970-01-01', 102), release_date) AS date, DATEDIFF(SECOND, CONVERT(DATETIME, '1970-01-01', 
                     102), edit_date) AS lastchanged, important
FROM            articles AS news WITH (NOLOCK)
WHERE        (status = 1) AND (release_date <= GETUTCDATE())
ORDER BY important DESC, date DESC, lastchanged DESC

This is an example of my data

id       date     lastchanged important
187346  1366070400  1345212540  1
187306  1365984000  1345290300  1
187330  1365984000  1342457880  1
187344  1363392000  1342461540  1
187343  1363392000  1342461300  1
187342  1363392000  1342459980  1
187339  1363392000  1342459800  1
187337  1363392000  1342458960  1
187335  1363392000  1342458720  1
187334  1363392000  1342458600  1
187332  1363392000  1342458060  1
187331  1363392000  1342457940  1
187327  1363392000  1342457340  1
187328  1363392000  1342457340  1
187326  1363392000  1342456860  1
187323  1363392000  1342456020  1
187322  1363392000  1342455480  1
187321  1363392000  1342454700  1
187316  1363392000  1342454580  1
187320  1363392000  1342454520  1

Solution

You could do something like this with row_number(). Performance probably is going to depend on how many active articles are present each day.

This codes creates a seq based on the sort you provide and then finds the sequence of the last Id value, which you can use to grab the top (x) rows.

declare @LastId INT,
        @PageSize INT

set @LastId = 187322
set @PageSize = 2

;with cte
as(
SELECT *, seq = ROW_NUMBER() over(ORDER BY important DESC, release_date DESC, edit_date DESC)
FROM @demo d
WHERE 
    status = 1
    AND release_date  (select seq from cte t2 where id = @LastId)
Order by t1.seq

Code Snippets

declare @LastId INT,
        @PageSize INT

set @LastId = 187322
set @PageSize = 2

;with cte
as(
SELECT *, seq = ROW_NUMBER() over(ORDER BY important DESC, release_date DESC, edit_date DESC)
FROM @demo d
WHERE 
    status = 1
    AND release_date <= GETUTCDATE()
)
select TOP (@PageSize) *
from cte t1
where t1.seq > (select seq from cte t2 where id = @LastId)
Order by t1.seq

Context

StackExchange Database Administrators Q#47400, answer score: 5

Revisions (0)

No revisions yet.