Ray Ray - 1 year ago 110
SQL Question

How do I write LINQ's .Skip(1000).Take(100) in pure SQL?

What is the SQL equivalent of the

method in LINQ?

For example: I would like to select rows 1000-1100 from a specific database table.

Is this possible with just SQL? Or do I need to select the entire table, then find the rows in memory? I'd ideally like to avoid this, if possible, since the table can be quite large.

Answer Source

In SQL Server 2005 and above you can use ROW_NUMBER function. eg.

USE AdventureWorks;
WITH OrderedOrders AS
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 51 AND 60; --BETWEEN is inclusive
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download