Paging using ROW_NUMBER() in SQL

Hi all..

I have a table -

ID	EmpCode	FName	LName	CreatedBy	CreatedOn
1	123	Ketan	Jadav	456	2013-09-09
2	456	Suresh	Sharma	456	2013-09-09
3	876	Lakshmi	Rao	456	2013-09-09
4	489	Reshma	Gandhi	456	2013-09-09

I have to load this table as -

select USERS.EmpCode, USERS.FName,USERS.LName,
(SELECT CONVERT(varchar(max),a.FName )+' '+CONVERT(varchar(max),a.LName )
from TBL_USERMASTER a
where a.EmpCode=CONVERT(numeric(25,0),USERS.TeamLead))as CreatedBy,
CONVERT(VARCHAR(10),TBL_USERMASTER.CreatedOn ,103) AS CreatedOn from USERS order by TBL_USERMASTER.EmpCode desc

I get the output as -

EmpCode

Fname

LName

CreatedBy

CreatedOn

123

Ketan

Jadav

Suresh Sharma

2013-09-09

456

Suresh

Sharma

Suresh Sharma

2013-09-09

876

Lakshmi

Rao

Suresh Sharma

2013-09-09

489

Reshma

Gandhi

Suresh Sharma

2013-09-09

Now, How do I implement paging in this query using Row_Number() ??

Eg -

'select * from (select ROW_NUMBER() over (order by Table1.ID) as ROW,
Table1.Col1 , Table1.Col2, ,CONVERT(VARCHAR(10),Table1.CreatedOn ,103)
AS CreatedOn , Table1.Col3
from Table1 as Table1Rows where ROW BETWEEN 1 and 50 order byTable1.ID'
-------------
I am not able to do paging in this query.

Hi Try this

DECLARE @PageIndex INT = 1 ,
      @PageSize INT = 1

DECLARE @table AS TABLE    (id INT,empcode INT,FName VARCHAR(64),LName VARCHAR(65),createdBy INT,createdDate DATE)
INSERT INTO @table
values (    1,
    123,
    'Ketan',
    'Jadav',
    456,
    '2013-09-09'),
(2,
    456,
    'Suresh',
    'Sharma',
    456,
    '2013-09-09'),
(3,
    876,
    'Lakshmi',
    'Rao',
    456,
    '2013-09-09'),
(4,
    489,
    'Reshma',
    'Gandhi',
    456,
    '2013-09-09');
WITH UserTable AS(
    select ROW_NUMBER() over (order by id) as ROWNum,empcode, FName,LName,
CONVERT(varchar(max),a.FName )+' '+CONVERT(varchar(max),a.LName ) as FullName
from @table as a)

SELECT empcode, FName,LName,FullName FROM UserTable
WHERE ( @PageIndex IS NULL
                      OR @PageSize IS NULL
                      OR ROWNum BETWEEN ( @PageIndex - 1 ) * @PageSize + 1
                               AND     ( ( ( @PageIndex - 1 ) * @PageSize + 1 )
                                         + @PageSize ) - 1
                    )

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Khan Abrar Ahmed12y ago
Hi Try this

DECLARE @PageIndex INT = 1 ,
      @PageSize INT = 1

DECLARE @table AS TABLE    (id INT,empcode INT,FName VARCHAR(64),LName VARCHAR(65),createdBy INT,createdDate DATE)
INSERT INTO @table
values (    1,
    123,
    'Ketan',
    'Jadav',
    456,
    '2013-09-09'),
(2,
    456,
    'Suresh',
    'Sharma',
    456,
    '2013-09-09'),
(3,
    876,
    'Lakshmi',
    'Rao',
    456,
    '2013-09-09'),
(4,
    489,
    'Reshma',
    'Gandhi',
    456,
    '2013-09-09');
WITH UserTable AS(
    select ROW_NUMBER() over (order by id) as ROWNum,empcode, FName,LName,
CONVERT(varchar(max),a.FName )+' '+CONVERT(varchar(max),a.LName ) as FullName
from @table as a)

SELECT empcode, FName,LName,FullName FROM UserTable
WHERE ( @PageIndex IS NULL
                      OR @PageSize IS NULL
                      OR ROWNum BETWEEN ( @PageIndex - 1 ) * @PageSize + 1
                               AND     ( ( ( @PageIndex - 1 ) * @PageSize + 1 )
                                         + @PageSize ) - 1
                    )