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