CODEWITHSUNDEEP
sqlsql-server-2008
DiH
DiH

Reputation: 491

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)

Upvotes: 7

Views: 18064

Answers (3)

sr3z
sr3z

Reputation: 410

do you mean offset clause ?

OFFSET excludes the first set of records. OFFSET can only be used with an ORDER BY clause. OFFSET with FETCH NEXT returns a defined window of records. OFFSET with FETCH NEXT is great for building pagination support.

The general syntax to exclude first n records is: 

   SELECT column-names
   FROM table-name
   ORDER BY column-names
   OFFSET n ROWS

Please refer to http://www.dofactory.com/sql/order-by-offset-fetch

Upvotes: 6

Abhishek Mishra
Abhishek Mishra

Reputation: 340

WITH T AS
(
SELECT TOP 30 name, 
       row_number() OVER (ORDER BY id) AS RN
FROM memberform 
ORDER BY id
)
SELECT 
       MAX(CASE WHEN RN <=10 THEN name END) AS Col1,
       MAX(CASE WHEN RN > 10 THEN name END) AS Col2
FROM T       
GROUP BY RN % 10

Upvotes: 0

marc_s
marc_s

Reputation: 754578

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..

Upvotes: 7

Related Questions