Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

What is the SQL equivalent of the .Skip() 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.

like image 758
Ray Avatar asked Nov 16 '09 20:11

Ray


People also ask

How do I edit 1000 rows in SQL?

When you right-click a table in SSMS, you can “Select Top 1000 Rows” and “Edit Top 200 Rows.” You can change how many rows are returned by changing the defaults. Change these values to whatever makes sense in your situation.


4 Answers

SQL Server 2012 and above have added this syntax:

SELECT *
FROM Sales.SalesOrderHeader 
ORDER BY OrderDate
OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLY
like image 195
John Gietzen Avatar answered Oct 16 '22 08:10

John Gietzen


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

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 51 AND 60; --BETWEEN is inclusive
like image 22
Dan Diplo Avatar answered Oct 16 '22 10:10

Dan Diplo


LINQ to SQL does this by using a ROW_NUMBER windowing function:

  SELECT a,b,c FROM 
   (SELECT a,b,c, ROW_NUMBER() OVER (ORDER BY ...) as row_number
    FROM Table) t0
   WHERE to.row_number BETWEEN 1000 and 1100;

This works, but the need to manufacture the row_number from the ORDER BY may result in your query being sorted on the server side and cause performance problems. Even when an index can satisfy the ORDER BY requirement, the query still has to count 1000 rows before startting to return results. All too often developers forget this and just throw a pagination control over a 5 mil rows table and wonder why the first page is returned so much faster than the last one...

None the less, using ROW_NUMBER() is probably the best balance between ease of use and good performance, provided you make sure you avoid the sort (the ORDER BY condition can be satisified by an index).

like image 22
Remus Rusanu Avatar answered Oct 16 '22 10:10

Remus Rusanu


Try this one:

select * from [Table-Name] order by [Column-Name] 
offset [Skip-Count] rows
FETCH NEXT [Take-Count] rows only

Example:

select * from Personals order by Id
offset 10 rows            --------->Skip 10
FETCH NEXT 15 rows only   --------->Take 15
like image 8
Fereydoon Barikzehy Avatar answered Oct 16 '22 08:10

Fereydoon Barikzehy