DiH DiH - 1 year ago 91
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 Source

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
        (list of columns),
        RowNum = ROW_NUMBER() OVER (ORDER BY -some-column-of-yours-)
    (list of columns)
    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..

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download