<p>Many applications have grids that display data from a database table one page at a time. Many of them also let the user pick the number of records per page, sort by any column, and navigate back and forth through the results.</p>

<p>What's a good algorithm to implement this pattern without bringing the entire table to the client and then filtering the data on the client. How do you bring just the records you want to display to the user?</p>

<p>Does LINQ simplify the solution?</p>

[{'answer_id': 10194, 'author': 'Michael Stum', 'author_id': 91, 'author_profile': 'https://Stackoverflow.com/users/91', 'pm_score': 5, 'selected': True, 'text': '<p>On MS SQL Server 2005 and above, <a href="http://msdn.microsoft.com/en-us/library/ms186734.aspx" rel="noreferrer">ROW_NUMBER()</a> seems to work:</p>\n\n<p><a href="http://blogs.x2line.com/al/archive/2005/11/18/1323.aspx" rel="noreferrer">T-SQL: Paging with ROW_NUMBER()</a></p>\n\n<pre><code>DECLARE @PageNum AS INT;\nDECLARE @PageSize AS INT;\nSET @PageNum = 2;\nSET @PageSize = 10;\n\nWITH OrdersRN AS\n(\n    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum\n          ,OrderID\n          ,OrderDate\n          ,CustomerID\n          ,EmployeeID\n      FROM dbo.Orders\n)\n\nSELECT * \n  FROM OrdersRN\n WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 \n                  AND @PageNum * @PageSize\n ORDER BY OrderDate\n         ,OrderID;\n</code></pre>\n'}, {'answer_id': 10197, 'author': 'Vaibhav', 'author_id': 380, 'author_profile': 'https://Stackoverflow.com/users/380', 'pm_score': 1, 'selected': False, 'text': '<p>Actually, LINQ has Skip and Take methods which can be combined to choose which records are fetched.</p>\n<p>Check those out.</p>\n<p>For DB: <a href="http://web.archive.org/web/20170901080057/http://www.singingeels.com:80/Articles/Pagination_In_SQL_Server_2005.aspx" rel="nofollow noreferrer">Pagination In SQL Server 2005</a></p>\n'}, {'answer_id': 10227, 'author': 'Mark Harrison', 'author_id': 116, 'author_profile': 'https://Stackoverflow.com/users/116', 'pm_score': 1, 'selected': False, 'text': '<p>Oracle Solution:</p>\n\n<pre><code>select * from (\n    select a.*, rownum rnum from (\n        YOUR_QUERY_GOES_HERE -- including the order by\n    ) a\n    where rownum &lt;= MAX_ROW\n ) where rnum &gt;= MIN_ROW\n</code></pre>\n'}, {'answer_id': 10282, 'author': 'Rory MacLeod', 'author_id': 1016, 'author_profile': 'https://Stackoverflow.com/users/1016', 'pm_score': 3, 'selected': False, 'text': "<p>I'd recommend either using LINQ, or try to copy what it does. I've got an app where I use the LINQ Take and Skip methods to retrieve paged data. The code looks something like this:</p>\n\n<pre><code>MyDataContext db = new MyDataContext();\nvar results = db.Products\n    .Skip((pageNumber - 1) * pageSize)\n    .Take(pageSize);\n</code></pre>\n\n<p>Running SQL Server Profiler reveals that LINQ is converting this query into SQL similar to:</p>\n\n<pre><code>SELECT [ProductId], [Name], [Cost], and so on...\nFROM (\n    SELECT [ProductId], [Name], [Cost], [ROW_NUMBER]\n    FROM (\n       SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS [ROW_NUMBER], \n           [ProductId], [Name], [Cost]\n       FROM [Products]\n    )\n    WHERE [ROW_NUMBER] BETWEEN 10 AND 20\n)\nORDER BY [ROW_NUMBER]\n</code></pre>\n\n<p>In plain English:<br>\n1. Filter your rows and use the ROW_NUMBER function to add row numbers in the order you want.<br>\n2. Filter (1) to return only the row numbers you want on your page.<br>\n3. Sort (2) by the row number, which is the same as the order you wanted (in this case, by Name).</p>\n"}, {'answer_id': 12799, 'author': 'Grzegorz Gierlik', 'author_id': 1483, 'author_profile': 'https://Stackoverflow.com/users/1483', 'pm_score': 1, 'selected': False, 'text': "<p>There are a few solutions which I use with MS SQL 2005.</p>\n\n<p>One of them is ROW_NUMBER(). But, personally, I don't like ROW_NUMBER() because it doesn't work for big results (DB which I work on is really big -- over 1TB data running thousands of queries in second -- you know -- big social networking site).</p>\n\n<p>Here are my favourite solution.</p>\n\n<p>I will use kind of pseudo code of T-SQL.</p>\n\n<p>Let's find 2nd page of users sorted by forename, surname, where each page has 10 records.</p>\n\n<pre><code>@page = 2 -- input parameter\n@size = 10 -- can be optional input parameter\n\nif @page &lt; 1 then begin\n    @page = 1 -- check page number\nend\n@start = (@page-1) * @size + 1 -- @page starts at record no @start\n\n-- find the beginning of page @page\nSELECT TOP (@start)\n    @forename = forename,\n    @surname = surname\n    @id = id\nFROM\n    users\nORDER BY\n    forename,\n    surname,\n    id -- to keep correct order in case of have two John Smith.\n\n-- select @size records starting from @start\nSELECT TOP (@size)\n    id,\n    forename,\n    surname\nFROM\n    users\nWHERE\n    (forename = @forename and surname = @surname and id &gt;= @id) -- the same name and surname, but bigger id\n    OR (forename = @forename and surname &gt; @surname) -- the same name, but bigger surname, id doesn't matter\n    OR (forename &gt; @forename) -- bigger forename, the rest doesn't matter\nORDER BY\n    forename,\n    surname,\n    id\n</code></pre>\n"}, {'answer_id': 12820, 'author': 'Adam Lassek', 'author_id': 1249, 'author_profile': 'https://Stackoverflow.com/users/1249', 'pm_score': 2, 'selected': False, 'text': "<p>LINQ combined with lambda expressions and anonymous classes in .Net 3.5 <em>hugely</em> simplifies this sort of thing.</p>\n\n<p>Querying the database:</p>\n\n<pre><code>var customers = from c in db.customers\n                join p in db.purchases on c.CustomerID equals p.CustomerID\n                where p.purchases &gt; 5\n                select c;\n</code></pre>\n\n<p>Number of records per page:</p>\n\n<pre><code>customers = customers.Skip(pageNum * pageSize).Take(pageSize);\n</code></pre>\n\n<p>Sorting by any column:</p>\n\n<pre><code>customers = customers.OrderBy(c =&gt; c.LastName);\n</code></pre>\n\n<p>Getting only selected fields from server:</p>\n\n<pre><code>var customers = from c in db.customers\n                join p in db.purchases on c.CustomerID equals p.CustomerID\n                where p.purchases &gt; 5\n                select new\n                {\n                    CustomerID = c.CustomerID,\n                    FirstName = c.FirstName,\n                    LastName = c.LastName\n                };\n</code></pre>\n\n<p>This creates a statically-typed anonymous class in which you can access its properties:</p>\n\n<pre><code>var firstCustomer = customer.First();\nint id = firstCustomer.CustomerID;\n</code></pre>\n\n<p>Results from queries are lazy-loaded by default, so you aren't talking to the database until you actually need the data. LINQ in .Net also greatly simplifies updates by keeping a datacontext of any changes you have made, and only updating the fields which you change.</p>\n"}, {'answer_id': 56989, 'author': 'Loofer', 'author_id': 5552, 'author_profile': 'https://Stackoverflow.com/users/5552', 'pm_score': 0, 'selected': False, 'text': '<p>There is a discussion about this <a href="https://web.archive.org/web/20211020131201/https://www.4guysfromrolla.com/webtech/042606-1.shtml" rel="nofollow noreferrer">Here</a></p>\n\n<p>The technique gets page number 100,000 from a 150,000 line database in 78ms</p>\n\n<blockquote>\n  <p>Using optimizer knowledge and SET ROWCOUNT, the first EmployeeID in the page that is requested is stored in a local variable for a starting point. Next, SET ROWCOUNT to the maximum number of records that is requested in @maximumRows. This allows paging the result set in a much more efficient manner. Using this method also takes advantage of pre-existing indexes on the table as it goes directly to the base table and not to a locally created table. </p>\n</blockquote>\n\n<p>I am afraid I am not able to judge if it is better than the current accepted answer.</p>\n'}, {'answer_id': 19610367, 'author': 'Lukas Eder', 'author_id': 521799, 'author_profile': 'https://Stackoverflow.com/users/521799', 'pm_score': 3, 'selected': False, 'text': '<p>There are essentially two ways of doing pagination in the database (I\'m assuming you\'re using SQL Server):</p>\n\n<h3>Using OFFSET</h3>\n\n<p>Others have explained how the <code>ROW_NUMBER() OVER()</code> ranking function can be used to perform pages. It\'s worth mentioning that SQL Server 2012 finally included support for the SQL standard <a href="http://technet.microsoft.com/en-us/library/gg699618.aspx" rel="nofollow noreferrer"><code>OFFSET .. FETCH</code></a> clause:</p>\n\n<pre><code>SELECT first_name, last_name, score\nFROM players\nORDER BY score DESC\nOFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY\n</code></pre>\n\n<p>If you\'re using SQL Server 2012 and backwards-compatibility is not an issue, you should probably prefer this clause as it will be executed more optimally by SQL Server in corner cases.</p>\n\n<h3>Using the SEEK Method</h3>\n\n<p>There is an entirely different, much faster, but less known way to perform paging in SQL. This is often called the "seek method" as described in <a href="http://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method/" rel="nofollow noreferrer">this blog post here</a>.</p>\n\n<pre><code>SELECT TOP 10 first_name, last_name, score\nFROM players\nWHERE (score &lt; @previousScore)\n   OR (score = @previousScore AND player_id &lt; @previousPlayerId)\nORDER BY score DESC, player_id DESC\n</code></pre>\n\n<p>The <code>@previousScore</code> and <code>@previousPlayerId</code> values are the respective values of the last record from the previous page. This allows you to fetch the "next" page. If the <code>ORDER BY</code> direction is <code>ASC</code>, simply use <code>&gt;</code> instead.</p>\n\n<p>With the above method, you cannot immediately jump to page 4 without having first fetched the previous 40 records. But often, you do not want to jump that far anyway. Instead, you get a much faster query that might be able to fetch data in constant time, depending on your indexing. Plus, your pages remain "stable", no matter if the underlying data changes (e.g. on page 1, while you\'re on page 4).</p>\n\n<p>This is the best way to implement paging when lazy loading more data in web applications, for instance.</p>\n\n<p>Note, the "seek method" is also called <a href="https://stackoverflow.com/a/3215973/521799">keyset paging</a>.</p>\n'}]
['https://Stackoverflow.com/questions/10190', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/373/']