How to get N rows starting from row M from sorted table in T-SQL

by

UPDATE If you you are using SQL 2012 new syntax was added to make this really easy. See Implement paging (skip / take) functionality with this query

I guess the most elegant is to use the ROW_NUMBER function (available from MS SQL Server 2005):

WITH NumberedMyTable AS
(
    SELECT
        Id,
        Value,
        ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
    FROM
        MyTable
)
SELECT
    Id,
    Value
FROM
    NumberedMyTable
WHERE
    RowNumber BETWEEN @From AND @To

More Related Contents:

  1. Calculate a Running Total in SQL Server
  2. Not equal != operator on NULL
  3. SET NOCOUNT ON usage
  4. How can I get column names from a table in SQL Server?
  5. How can I select the first day of a month in SQL?
  6. Truncate (not round) decimal places in SQL Server
  7. Select statement to find duplicates on certain fields
  8. Is it possible to specify condition in Count()?
  9. Getting only Month and Year from SQL DATE
  10. Getting list of tables, and fields in each, in a database
  11. WHERE Clause vs ON when using JOIN
  12. Is it possible to select sql server data using column ordinal position
  13. @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity
  14. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  15. How to create a PivotTable in Transact/SQL?
  16. Dynamic SQL to generate column names?
  17. Pivot data in T-SQL
  18. Extracting hours from a DateTime (SQL Server 2005)
  19. Split one column value into multiple column values
  20. How to compare datetime with only date in SQL Server
  21. I want to use CASE statement to update some records in sql server 2005
  22. SQL server select distinct rows using most recent value only
  23. Generate all combinations in SQL
  24. How to return a incremental group number per group in SQL
  25. How to search SQL column containing JSON array
  26. Eliminate and reduce overlapping date ranges
  27. scope_identity vs ident_current
  28. COALESCE Function in TSQL
  29. Convert bigint to datetime
  30. Replacing NULL and empty string within Select statement

Leave a Comment