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

User · Answer

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

User · Answer

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  gt   Start AND RowNumber  lt    End ORDER BY PostId

User · Answer

OFFSET     10 ROWS       -- skip 10 rows FETCH NEXT 10 ROWS ONLY  -- take 10 rows   use this in the end of your select syntax

User · Answer

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

User · Answer

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

User · Answer

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 gt 10   Source  https   forums asp net post 4033909 aspx

[sql] Implement paging (skip / take) functionality with this query

Examples related to sql

Examples related to sql-server

Examples related to join

[sql] Implement paging (skip / take) functionality with this query

Examples related to sql

Examples related to sql-server

Examples related to join

Examples related to pagination