Implement paging (skip / take) functionality with this query

by

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

More Related Contents:

  1. How to display data in JOIN, and return null value? [closed]
  2. SQL update from one Table to another based on a ID match
  3. What is the best way to paginate results in SQL Server
  4. Equivalent of LIMIT and OFFSET for SQL Server?
  5. Can I use CASE statement in a JOIN condition?
  6. Difference between JOIN and INNER JOIN
  7. LIMIT 10..20 in SQL Server
  8. Difference between left join and right join in SQL Server [duplicate]
  9. efficient way to implement paging
  10. Natural join in SQL Server
  11. Joining multiple tables in SQL
  12. SQL Server 2008 paging methods?
  13. T-SQL Skip Take Stored Procedure
  14. SQL Inner Join On Null Values
  15. Removing duplicate rows (based on values from multiple columns) from SQL table
  16. How to calculate age (in years) based on Date of Birth and getDate()
  17. Does the join order matter in SQL?
  18. What is the difference between JOIN and UNION?
  19. How to get difference between two rows for a column field?
  20. How can I combine multiple rows into a comma-delimited list in SQL Server 2005?
  21. Call a set-returning function with an array argument multiple times
  22. SQL : in clause in stored procedure:how to pass values
  23. ORA – 00933 confusion with inner join and “as”
  24. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  25. Insert Update stored proc on SQL Server
  26. TSQL – How to use GO inside of a BEGIN .. END block?
  27. SQL – How do I get only the numbers after the decimal?
  28. How to retrieve records for last 30 minutes in MS SQL?
  29. Connecting to Oracle Database using Sql Server Integration Services
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment