Implement paging (skip / take) functionality with this query

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 hereOFFSET     10 ROWS       -- skip 10 rowsFETCH NEXT 10 ROWS ONLY; -- take 10 rows

If we want to skip ORDER BY we can use

SELECT col1, col2, ...  ... ORDER BY CURRENT_TIMESTAMPOFFSET     10 ROWS       -- skip 10 rowsFETCH 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 2012SELECT PostId FROM         ( SELECT PostId, MAX (Datemade) as LastDate            from dbForumEntry             group by PostId         ) SubQueryAlias order by LastDate descOFFSET 10 ROWS -- skip 10 rowsFETCH 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 2008DECLARE @Start INTDECLARE @End INTSELECT @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, LastDateFROM PostCTEWHERE RowNumber > @Start AND RowNumber <= @EndORDER 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 rowsFETCH 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_NUMfrom tablename) xwhere ROW_NUM>10

Source: https://forums.asp.net/post/4033909.aspx