CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Eyad Salah
Eyad Salah

Reputation: 1103

Select rows after a specific ID

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

Upvotes: 1

Views: 2955

Answers (2)

Amit Singh
Amit Singh

Reputation: 8109

;WITH CTE( ROW,ID, 
                [date], 
                [lastchanged], 
                IMPORTANT ) AS
(
SELECT rOW_NUMBER() OVER(oRDER BY IMPORTANT DESC, 
          DATE DESC, 
          LASTCHANGED DESC ) AS rOW,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() ) 
                ) 

SELECT TOP 20 ID, 
                [date], 
                [lastchanged], 
                IMPORTANT FROM CTE WHERE rOW>cOALESCE((sELECT rOW FROM CTE WHERE iD=1871),0);

Here is Example of this.In the example i select only top 5 so enter any id and you will that see it will fetch next five records.

Sql Fiddle Demo -Enter wrong id for first like i here i entred -1 than you will get first five records.

Sql Fiddle Demo -Enter id=187339 since it last id for first page

Sql Fiddle Demo -Enter id=187331 since it last id for second page and so on

Upvotes: 1

jpw
jpw

Reputation: 44891

If what you want to do is show 20 posts at a time in some application and to be able to fetch then next 20 posts until the end of the results maybe using the OFFSET FETCH clause might work.

The initial query would be:

SELECT        
    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
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;

and subsequent queries would increase the offsetvalue:

SELECT        
    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
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

Upvotes: 2

Related Questions