DiH DiH - 3 months ago 7
SQL Question

Select next 20 rows after top 10

I'm trying to select next 20 rows after top 10 rows.

select TOP 20 *
from memberform
where Row_Number over(10)

Answer

You need to use something like a CTE (Common Table Expression) and a ROW_NUMBER to define row numberings for your data set - then select from that numbered CTE for the set of rows you want:

;WITH PagingCte AS
(
    SELECT 
        (list of columns),
        RowNum = ROW_NUMBER() OVER (ORDER BY -some-column-of-yours-)
    FROM
        dbo.memberform 
)
SELECT
    (list of columns)
FROM
    PagingCte
WHERE
    RowNum BETWEEN 10 AND 29

In the inner ROW_NUMBER() window function, you need to define an ordering which defines how the rows are numbered (order by date, or by ID, or whatever makes sense for you).

Without an explicit ordering, there is no next 20 after the first 10 to be had..

Comments