Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 212
George2 Avatar asked Jun 21 '09 12:06

George2


People also ask

How do I select a specific RANGE of a row in SQL?

MySQL select rows by range using ROW_NUMBER() function The ROW_NUMBER() is a window function in MySQL that assigns a sequential number to each row. Hence we can use the row_number() function to select rows in a particular range.

How do I select a RANGE in a table in SQL?

The SQL BETWEEN Operator The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.

How can I get 100 to 200 records in SQL?

select <column list you want> from <your table name> order by ProductName offset 100 rows fetch next 100 rows only; That will skip the first 100 rows (in order by ProductName) and return the next 100 rows.


2 Answers

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
like image 175
Marc Gravell Avatar answered Oct 09 '22 02:10

Marc Gravell


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

like image 21
marc_s Avatar answered Oct 09 '22 02:10

marc_s