SQL - Limit results

Question

I was wondering if you could do something like this is Microsoft SQL Server 2k8 R2

Say I have a query which returns 100 rows of data.

Is their a way I can pass in some variables for example @lower_limit and @upper_limit.

Then I want the query to record the rows between the lower and upper limit

For example:

@lower_limit = 5
@upper_limt  10

Would return me rows 5 - 10 from the 100 records.

possible duplicate of [LIMIT 10..20 in sqlserver](http://stackoverflow.com/questions/971964/limit-10-20-in-sqlserver) — mellamokb, Nov 18 '11 at 16:59

Cᴏʀʏ · Accepted Answer · 2011-11-18T19:16:34.480

6

You can assign a ROW_NUMBER() over your result set and then use the BETWEEN statement to limit the rows.

A contrived example:

WITH data AS
(
    SELECT
        ID
       ,YourColumn
       ,ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
    FROM
        YourTable
)
SELECT 
    *
FROM
    data
WHERE
    RowNum BETWEEN 5 AND 10

EDIT: For standard paging, here's exactly the technique I use in all the applications I develop:

DECLARE @PageNumber int = /* The page number you want */
DECLARE @PageSize int = /* The number of records per page */
WITH paged AS
(
    SELECT
         ROW_NUMBER() OVER(ORDER BY [OrderByColumns]) AS RowNum
        ,*
    FROM
        [YourSource]
)
SELECT 
     [Column1]
    ,[Column2]
    ,...
FROM 
    paged
WHERE 
    RowNum BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
ORDER BY 
    [OrderByColumns] -- Same as used in ROW_NUMBER()

edited Nov 18 '11 at 19:16

answered Nov 18 '11 at 16:58

Cᴏʀʏ

105,112
20
162
194

+1 Definitely! I created [a post](http://codecrafter.blogspot.com/2008/08/painful-sql-server-pagination.html) about this a while ago. – Jordão Nov 18 '11 at 17:00
That is unreal Cory, many thanks, so much better than the way the dude before me here implemented the pagination for this site. He was calling the SP everytime and then limiting what shows in the code. – swade1987 Nov 18 '11 at 17:28
@user1052764: I edited my answer to show you a full technique for paging that I use. – Cᴏʀʏ Nov 18 '11 at 19:17

score 0 · Answer 2 · answered Nov 18 '11 at 16:59

0

select *
from
(
    select *, row_number() over(order by someColToOrderBy) RowNum
    from yourTable
) a
where RowNum between @lower_limit and @uppder_limit

answered Nov 18 '11 at 16:59

score 0 · Answer 3 · answered Nov 18 '11 at 16:59

0

Something like this should work:

SELECT  ID, Foo, Bar
FROM    (
            SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS Row, ID, Foo, Bar 
            FROM SomeTable
        ) 
        tmp
WHERE   Row >= @RowRangeStart AND Row <= @RowRangeEnd

answered Nov 18 '11 at 16:59

James Johnson

45,496
8
73
110

SQL - Limit results

3 Answers3