We can implement the paging using the Linq Skip and Take method.
The OFFSET FETCH clause allows you to skip N first rows in a result set before starting to return any rows. In this syntax: The ROW and ROWS , FIRST and NEXT are the synonyms. Therefore, you can use them interchangeably.
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
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: https://technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%29.aspx
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: https://forums.asp.net/post/4033909.aspx
OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows
use this in the end of your select syntax. =)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With