Implement paging (skip / take) functionality with this query

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

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?

[1]:

[To see links please register here]

In order to do this in SQL Server, you must order the query by a column, so you can specify the rows you want.

Example:

select * from table order by [some_column]
offset 10 rows
FETCH NEXT 10 rows only

And you can't use the "TOP" keyword when doing this.

You can learn more here:

[To see links please register here]

In **SQL Server 2012** it is very very easy

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

If we want to skip ORDER BY we can use


SELECT col1, col2, ...
...
ORDER BY CURRENT_TIMESTAMP
OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

*(I'd rather mark that as a hack - but it's used, e.g. by NHibernate. To use a wisely picked up column as ORDER BY is preferred way)*

to answer the question:

--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.

*But I guess, that you are not using **SQL Server 2012**, right*? In previous version it is a bit (little bit) difficult. Here is comparison and examples for all SQL server versions: [here][1]


So, this could 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
)
SELECT PostId, LastDate
FROM PostCTE
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY PostId

[1]:

[To see links please register here]

OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

use this in the end of your select syntax. =)

**SQL 2008**

Radim Köhler's answer works, but here is a shorter version:

select top 20 * from
(
select *,
ROW_NUMBER() OVER (ORDER BY columnid) AS ROW_NUM
from tablename
) x
where ROW_NUM>10

Source:

[To see links please register here]

You can use **nested query** for pagination as follow:

Paging from 4 Row to 8 Row where **CustomerId** is **primary key**.

SELECT Top 5 * FROM Customers
WHERE Country='Germany' AND CustomerId Not in (SELECT Top 3 CustomerID FROM Customers
WHERE Country='Germany' order by city)
order by city;

The fix is to modify your EDMX file, using the XML editor, and change the value of **ProviderManifestToken** from **2012** to **2008**. I found that on line 7 in my EDMX file. After saving that change, the paging SQL will be generated using the “old”, SQL Server 2008 compatible syntax.



*My apologies for posting an answer on this very old thread. Posting it for the people like me, I solved this issue today.*