5

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

select TOP 20 * 
from memberform 
where Row_Number over(10)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DiH
  • 451
  • 2
  • 8
  • 18
  • Since a table has no inherent order, by what column(s) do you want to order? – Tim Schmelter Sep 01 '16 at 08:57
  • I want to select next 20 rows after first 10 rows.Isnt there row increment into sql? – DiH Sep 01 '16 at 08:59
  • How have you determined the first 10 rows of a table? `SELECT TOP 10 * FROM T` returns 10 arbitrary rows. Maybe you'll get ten other on the next query. You need an `ORDER BY` – Tim Schmelter Sep 01 '16 at 09:03

3 Answers3

6

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Cant i use between? select top 20 * from table where rows between 20 and 40? – DiH Sep 01 '16 at 09:02
  • @DiH: **no** - there's no automatic, built-in "row numbering" in SQL Server. And again: **without explicit ordering**, there is no order, and therefore you cannot get the "next 10" - the next 10 **ordered by WHAT?** – marc_s Sep 01 '16 at 09:02
  • Cant i use select * ? – DiH Sep 01 '16 at 09:10
6

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

sr3z
  • 400
  • 1
  • 2
  • 10
0
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
Serg
  • 22,285
  • 5
  • 21
  • 48
Abhishek Mishra
  • 340
  • 4
  • 14