Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Any idea how I can get the offset of the row by ID without having to do multiple queries? –  ComputerJy Aug 1 '13 at 8:03
    
How would last offset by ID work? The data isn't ordered by ID, and don't forget that it could have changed since the last ID was derived. If you want to do paging, don't try to remember what the last ID was, have the application actually tell you what the last page was. –  Aaron Bertrand Aug 1 '13 at 13:22
2  
You will have to use the row number function. This will provide a sequence to each row. You can then determine the position of last row via the sequence and then add 20 to it –  Adam Haines Aug 1 '13 at 16:46
    
@AdamHaines: In SQL Server 2012, it is possible to solve this without ROW_NUMBER. Please see my answer if you are interested. –  Andriy M Aug 2 '13 at 17:07
add comment

3 Answers

up vote 4 down vote accepted

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 <= GETUTCDATE()
)
select TOP (@PageSize) *
from cte t1
where t1.seq > (select seq from cte t2 where id = @LastId)
Order by t1.seq
share|improve this answer
add comment

Minor notes:

  • I think that you should (for efficiency) order by the table columns, not by the computed columns: ORDER BY important DESC, release_date DESC, edit_date DESC.

  • There is no need for conversions when you use the 'YYYYMMDD' format for dates.

For your other question, you could use the FETCH ... OFFSET syntax, added in 2012 version:

SELECT     id,
           date = DATEDIFF(SECOND, '19700101', release_date),
           lastchanged = DATEDIFF(SECOND, '19700101', edit_date),
           important
FROM       articles AS news
WHERE      (status = 1)
  AND      (release_date <= GETUTCDATE())
ORDER BY   important DESC, 
           release_date DESC, 
           edit_date DESC 
OFFSET      0 ROWS                     -- for the next page, use: OFFSET 20 ROWS
FETCH NEXT 20 ROWS ONLY ;

With big offsets though, it would probably be more efficient to use a query that uses the results from the previous run.

SELECT TOP (20)
           id,
           date = DATEDIFF(SECOND, '19700101', release_date),
           lastchanged = DATEDIFF(SECOND, '19700101', edit_date),
           important
FROM       articles AS news
       JOIN
           ( SELECT *
             FROM articles 
             WHERE id = ?            -- the last id from previous run
           ) AS pr
         ON 
            (    news.important = pr.important
             AND news.release_date = pr.release_date
             AND news.edit_date = pr.edit_date 
             AND news.id < pr.id 
            OR   news.important = pr.important
             AND news.release_date = pr.release_date
             AND news.edit_date < pr.edit_date
            OR   news.important = pr.important
             AND news.release_date < pr.release_date
            OR   news.important < pr.important
            )
WHERE      (status = 1)
  AND      (release_date <= GETUTCDATE())
ORDER BY   important DESC, 
           release_date DESC, 
           edit_date DESC,
           id DESC ;
share|improve this answer
    
Thanks for the tips, but the problem is I don't have a page index to use as an offset –  ComputerJy Aug 1 '13 at 8:19
add comment

Another method would be to use conditional window aggregation with an ORDER BY and a ROW clauses to detect, for every row, presence of the specified ID in the rows prior to the current one, returning the fact as a flag column and then filtering on that flag:

WITH marked AS (
  SELECT
    id,
    date        = DATEDIFF(SECOND, CONVERT(DATETIME, '1970-01-01', 102), release_date),
    lastchanged = DATEDIFF(SECOND, CONVERT(DATETIME, '1970-01-01', 102), edit_date),
    important,
    candisplay  = MAX(CASE id WHEN @id THEN 1 END)
                  OVER (ORDER BY important DESC, date DESC, lastchanged DESC
                        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
  FROM articles AS news
  WHERE (status = 1) AND (release_date <= GETUTCDATE())
)
SELECT TOP (@PageSize)
FROM marked
WHERE candisplay = 1
ORDER BY important DESC, date DESC, lastchanged DESC
;
share|improve this answer
    
I'll give this a try. It seems interesting –  ComputerJy Aug 2 '13 at 20:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.