CODEWITHSUNDEEP
c#.netsql-server-2008ado.net
George2
George2

Reputation: 45801

retrieve specific range of rows in a SQL Server table

I have a table structure like (OrderID [uniqueidentifier], OrderDesciption [nvarchar]), I am using ADO.Net + C# + VSTS 2008 + SQL Server 2008. The table is big, and I want to let client give me two inputs, begin range index and end range index, and I will return specific rows of the table which is in the range (between begin range index and end range index).

For example, if the client inputs to me 50, 100, and I want to return the 50th row until the 100th row.

thanks in advance, George

Upvotes: 7

Views: 23555

Answers (4)

Marc Gravell
Marc Gravell

Reputation: 1063338

You can use ROW_NUMBER in TSQL (2005 onwards) to do this:

SELECT  ID, Foo, Bar
FROM     (SELECT  ROW_NUMBER() OVER (ORDER BY ID ASC) AS Row,
          ID, Foo, Bar
FROM    SomeTable) tmp
WHERE   Row >= 50 AND Row <= 100

Or with LINQ-to-SQL etc:

var qry = ctx.Table.Skip(50).Take(50); // or similar

Upvotes: 15

kiewic
kiewic

Reputation: 16440

Try this, the result will be ordered by OrderID column. Change MyTable with your table. 

SELECT * 
FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderID) AS row FROM MyTable) a 
WHERE row > 50 AND row <= 100

The selected rows are from 50 to 100, but notice that the 50th row is not included in the result.

Upvotes: 2

marc_s
marc_s

Reputation: 754963

Basically, your best bet in SQL Server 2005 and 2008 is a CTE - Common Table Expression - with a ROW_NUMBER() function in it - something like this:

WITH MyOrders AS
(
  SELECT
    OrderID,
    OrderDescription,
    ROW_NUMBER() OVER (ORDER BY OrderID) as 'RowNum'
  FROM YourOrders
)
SELECT * FROM MyOrders
WHERE RowNum BETWEEN 50 AND 100

But this requires a useful and suitable ORDER BY clause, and ordering by a GUID is really not a good idea. DATETIME or an ever-increasing ID would be best.

Marc

Upvotes: 4

balint
balint

Reputation: 3431

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderId) as row FROM Orders) a WHERE row > 5 and row <= 10

Upvotes: 1

Related Questions