pagination in sql

1SELECT col1, col2, ...
2 FROM ...
3 WHERE ... 
4 ORDER BY -- this is a MUST there must be ORDER BY statement
5-- the paging comes here
6OFFSET     10 ROWS       -- skip 10 rows
7FETCH NEXT 10 ROWS ONLY; -- take 10 rows

1SELECT * FROM (
2    SELECT a.*, rownum rn
3    FROM (
4        SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
5        ORDER BY OrderDate DESC, ShippingDate DESC
6    ) a
7    WHERE rownum < ((pageNumber * pageSize) + 1 )
8)
9WHERE rn >= (((pageNumber-1) * pageSize) + 1);

1SELECT * FROM SampleFruits
2ORDER BY Id
3OFFSET 0 ROWS 
4FETCH NEXT 7 ROWS ONLY

1--CREATING A PAGING WITH OFFSET and FETCH clauses IN "SQL SERVER 2012"
2DECLARE @PageNumber AS INT, @RowspPage AS INT
3SET @PageNumber = 2
4SET @RowspPage = 10 
5SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE
6FROM TB_EXAMPLE
7ORDER BY ID_EXAMPLE
8OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
9FETCH NEXT @RowspPage ROWS ONLY;
10