How to do pagination in SQL

In our previous post we learned about Row_Number() function SQL.

Now consider, we some 100 records in the table, and you required to display the record using pagination, meaning, you have to display only 10 records at a time and when you click on the next tab in the UI, the same procedure should return the next records, i.e., the records starting from 11 to 20.

How can we achieve this?

Recollect our previous example,

CREATE TABLE [dbo].[TESTTABLE]( [STATUS] [nvarchar](50) NULL )

Insert few more records in that table, and make the table have more than 10 row in it.

INSERT INTO TESTTABLE VALUES ('MOHAN'),('RAJ'),('GANESH'),('JOHN'),('JIJO'),('ROCK'),('MELLON'),
(
'MOHANA'),('RAJA'),('GANESHA'),('JOHNNOBEL'),('JENSI'),('RICKTER'),('ROSE')
Now after inserting these records, our table would have in total 17 rows.

Ok our work starts here..

Just apply the Row_Number() function at first as below,

SELECT ROW_NUMBER() OVER(ORDER BY STATUS) [ROW], * FROM TESTTABLE

This will give the serial number of the records from 1 to 17.

Now, put the above query as a Common Table Expression [Learn about CTE here... MSDN] as below,

WITH CTE AS(SELECT ROW_NUMBER() OVER(ORDER BY STATUS) [ROW], * FROM TESTTABLE)SELECT * FROM CTE WHERE ROW > 11
Now whats the result?

ROW                   STATUS
--------                   --------------
12                        RAJ
13                        RAJA
14                        RICKTER
15                        ROCK
16                        ROSE
17                        SIVA
thats it............ we have achieved our pagination.

Now, all that you have to do is, write a logic to get the next tab count, which gives you exact place of fetch i.e., which set of record need to be fetched. Once that is done our sql query gives our desired pagination result.
Here it goes......

DECLARE @NOOFROWS AS INT SET @NOOFROWS = 5; --No of records per page
DECLARE @TABCOUNT AS INT SET @TABCOUNT = 2; --Next tab count
WITH CTE AS(SELECT ROW_NUMBER() OVER(ORDER BY STATUS) [ROW], * FROM TESTTABLE)SELECT * FROM CTE WHERE ROW
> (@NOOFROWS * (@TABCOUNT - 1)) AND ROW
<= (@TABCOUNT * @NOOFROWS)

Now this will give you the result as below,

ROW            STATUS
---------           -------------
6                  JIJO
7                  JOHN
8                  JOHNNOBEL
9                  MELLON
10                MOHAN

ROW_NUMBER() function in SQL

Lets learn a very basic feature in SQL today.

ROW_NUMBER()

If i have to put a serial number for my result retrieved through a SELECT Statement, i can use Row_Number() function for the same.

Example:

Just consider i have a table like below.

 CREATE TABLE [dbo].[TESTTABLE]( [STATUS] [nvarchar](50) NULL )

insert into TESTTABLE values ('GOPI'), ('DINESH'), ('SIVA')

Now we have a table with three rows and a single column which has Status as the field in it.

If we put SELECT * FROM TESTTABLE, we will get the following result

STATUS
-----------------------
GOPI
DINESH
SIVA

Now, when I apply the Row_Number() function in it as follows,

SELECT ROW_NUMBER() over(order by status) [Row], * FROM TESTTABLE

My output will be as below,

Row                    STATUS
----------------- --------------------------------------------------
1                          DINESH
2                          GOPI
3                          SIVA

Now, this is ROW_NUMBER() function, it adds the number of rows retrieved in a sequencial way for each rows.

For using ROW_NUMBER() function, we have to use OVER key word in which we have to provide a order by clause for a column name of the table on which we put the select query.

Having said this, now if you want to start the ROW number sequence from a certain value what can you do? Just add the required integer value on the Row_number function result as below,

SELECT ROW_NUMBER() over(order by status) + 2 [Row] , * FROM TESTTABLE

The result will be as below,

Row STATUS
----- ----------
3      DINESH
4      GOPI
5      SIVA

Hope this would help you....