Implement paging (skip / take) functionality with this query

Question

I have been trying to understand a little bit about how to implement custom paging in SQL, for instance reading articles like this one.

I have the following query, which works perfectly. But I would like to implement paging with this one.

SELECT TOP x PostId FROM ( SELECT PostId, MAX (Datemade) as LastDate

 from dbForumEntry 

 group by PostId ) SubQueryAlias

 order by LastDate desc

What is it I want

I have forum posts, with related entries. I want to get the posts with the latest added entries, so I can select the recently debated posts.

Now, I want to be able to get the "top 10 to 20 recently active posts", instead of "top 10".

What have I tried

I have tried to implement the ROW functions as the one in the article, but really with no luck.

Any ideas how to implement it?

Answer 1

In SQL Server 2012, you can use this code:

SELECT col1, col2, ...

 FROM ...

 WHERE ... 

 ORDER BY -- this is a MUST there must be ORDER BY statement

-- the paging comes here

OFFSET     10 ROWS       -- skip 10 rows

FETCH NEXT 10 ROWS ONLY; -- take 10 rows

But, If you want to skip ORDER BY then you can use the below code

SELECT col1, col2, ...

  ...

 ORDER BY CURRENT_TIMESTAMP

OFFSET     10 ROWS       -- skip 10 rows

FETCH NEXT 10 ROWS ONLY; -- take 10 rows

For SQL SERVER 2012 

--SQL SERVER 2012

SELECT PostId FROM 

        ( SELECT PostId, MAX (Datemade) as LastDate

            from dbForumEntry 

            group by PostId 

        ) SubQueryAlias

 order by LastDate desc

OFFSET 10 ROWS -- skip 10 rows

FETCH NEXT 10 ROWS ONLY; -- take 10 rows

New key words offset and fetch next (just following SQL standards) were introduced.It seems like you are not using SQL Server 2012. In previous version it is a bit (little bit) difficult. 

Here is comparison and examples for all SQL server version.

So, the below code will  work in SQL Server 2008:

-- SQL SERVER 2008

DECLARE @Start INT

DECLARE @End INT

SELECT @Start = 10,@End = 20;

;WITH PostCTE AS 

 ( SELECT PostId, MAX (Datemade) as LastDate

   ,ROW_NUMBER() OVER (ORDER BY PostId) AS RowNumber

   from dbForumEntry 

   group by PostId