sql server pagination

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