CODEWITHSUNDEEP
sql-server
Hubert Solecki
Hubert Solecki

Reputation: 2771

How to get row between an intervalle of row number?

Is it possible in sql server to get the lines from row 10 to row 20 for example ? I mean, I'm getting more than 1800 rows by querying the database and then i would like to display them 10 by 10.

Upvotes: 1

Views: 323

Answers (3)

kol
kol

Reputation: 28698

Example:

WITH ResultSetWithRowNumber AS 
(
  SELECT ROW_NUMBER() OVER(ORDER BY MyField1) AS RowNumber, MyField1, MyField2
  FROM MyTable
  WHERE MyField2 = SomeValue
)
SELECT RowNumber, MyField1, MyField2
FROM ResultSetWithRowNumber 
WHERE RowNumber BETWEEN 10 AND 20

MSDN

Upvotes: 0

podiluska
podiluska

Reputation: 51494

Or in SQL 2012+, use OFFSET\FETCH

....
ORDER BY ...
OFFSET 10 ROWS
FETCH NEXT 10 ROWS;

See http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.110%29.aspx

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

Use a CTE to add a column that represents row number of each row and then filter by that column.

;WITH MyCTE AS
(
    SELECT    *,
              ROW_NUMBER() OVER(ORDER BY ID) RowNum
    FROM      Table
)

SELECT *
FROM   MyCTE
WHERE  RowNum BETWEEN 10 AND 20

Upvotes: 2

Related Questions