Selecting n Number of Rows Starting From The nth Row

Question

SQL-noobie here that wants to modify the following stored procedure into one that selects a specified amount of rows starting from the specified row. For example I want to select 5 rows starting from the 10th row, ie 10, 11, 12, 13, 14, 15.

Here is what my SP looks like atm:

SELECT Posts.Slug, Comments.commentId, Comments.[date], Comments.name, Comments.[text]
FROM Comments 
INNER JOIN Posts ON Comments.postId = Posts.Id
WHERE Comments.approved = 0

I don't need to order the results as I want to read sequentially, in-case that matters.

Thanks!

Also what you want to is called [paging](http://stackoverflow.com/questions/548475/efficient-way-to-implement-paging). — Aaron Bertrand, Jul 29 '13 at 19:20
Sequentially as in the order by which they were stored in the database. — TheGateKeeper, Jul 29 '13 at 19:20
In your case, the ordering does matter as without order, the results can vary on different executions. You're basically looking for paging. In this case you just need to order, and then use RowNum to get slices: http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server — Maurice Reeves, Jul 29 '13 at 19:21
"I don't need to order the results as I want to read sequentially, in-case that matters" This sentence contridicts itself, you'd be better off removing it. — Khan, Jul 29 '13 at 19:21
SQL Server doesn't maintain the order by which they were stored in the database. Consider a table an unordered bag of rows. If you want to order by something, you need to tell SQL Server what that is. In this case I assume `[date]` as long as that's just a poor name and it actually includes time as well. — Aaron Bertrand, Jul 29 '13 at 19:21
I was under the assumption that rows are stored in sequential order and then retrieved in that same order. I will start ordering by date then, thanks for the tip! — TheGateKeeper, Jul 30 '13 at 09:17

Daniel Neal · Accepted Answer · 2013-07-29T19:36:02.780

In SQL Server 2005/2008, you'll need to use a row number, as Maurice mentioned. The LIMIT syntax unfortunately doesn't work. The query will look like this (assuming you want to order by date)

WITH numbered AS
(
    SELECT 
       Posts.Slug, 
       Comments.commentId, 
       Comments.[date],
       Comments.name, Comments.[text],  
       ROW_NUMBER() OVER (ORDER BY date) as rownum
    FROM Comments 
    INNER JOIN Posts ON Comments.postId = Posts.Id
    WHERE Comments.approved = 0
) 
SELECT * FROM numbered WHERE rownum BETWEEN @startrow AND @endrow

The above assumes @startrow and @endrow are parameters passed into your stored procedure.

Thanks man it works, I just had to change the last part as I didn't want to select the `rownum` :). — TheGateKeeper, Jul 30 '13 at 10:33

Hart CO · Answer 2 · 2013-07-29T19:41:30.717

4

If you happen to be using SQL Server 2012 you can use OFFSET and FETCH for paging, but they require an ORDER BY clause :

SELECT Posts.Slug, Comments.commentId, Comments.[date], Comments.name, Comments.[text]
FROM Comments 
INNER JOIN Posts ON Comments.postId = Posts.Id
WHERE Comments.approved = 0
ORDER BY [date]
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY

If not using SQL Server 2012, the ROW_NUMBER() approach is how you should go.

If you actually want arbitrary ordering, you can use ORDER BY (SELECT 1) but consistency cannot be guaranteed without explicit ordering.

edited Jul 29 '13 at 19:41

answered Jul 29 '13 at 19:33

Hart CO

34,064
6
48
63

Thanks for the answer, but I decided to go with the `ROW_NUMBER()` thing for better compatibility. Still this works so cheers! – TheGateKeeper Jul 30 '13 at 10:34

score -2 · Answer 3 · answered Jul 29 '13 at 19:29

-2

Indeed the fastest for sql server is

SELECT col1, col2 
FROM (
    SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
    FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow

Take a look here

answered Jul 29 '13 at 19:29

Lefteris Bab

787
9
19

2

This is functionally no different than Daniel Neal's answer – swasheck Jul 29 '13 at 19:35
@swasheck It is not actually the same. And I have added you a reference for further reading and understanding the statement. Sorry for that anyway – Lefteris Bab Jul 29 '13 at 19:44
2

How is it different, except that you used a derived table instead of a CTE, and you didn't bother using the OP's actual table names? – Aaron Bertrand Jul 29 '13 at 19:49
@LefterisBab They're both table expressions. Thanks for the link to a 2006 ASP article, though. – swasheck Jul 29 '13 at 19:50
For the record, I didn't downvote you. I just put a comment noting that it was a poor duplicate of an extant answer. – swasheck Jul 29 '13 at 19:53
1

No hard feelings I searched to stackoverflow and found this: http://stackoverflow.com/questions/187998/row-offset-in-sql-server So I this this question is duplicate. Wright ? – Lefteris Bab Jul 29 '13 at 19:57
1

Yes. That's why we VtC it. – swasheck Jul 29 '13 at 19:57

score -3 · Answer 4 · answered Jul 29 '13 at 19:30

Use ROW_NUMBER() function in t-sql. See MSDN

USE AdventureWorks2012;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber
    FROM Sales.SalesOrderHeader 
) 
SELECT SalesOrderID, OrderDate, RowNumber  
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

Selecting n Number of Rows Starting From The nth Row

4 Answers4