An Approximate Pagination with Non-Break Group With T-SQL


I have been asked about this question with Oracle. I came across this discussion at this thread:
http://stackoverflow.com/questions/3438228/oracle-how-to-group-rows-for-pagination

I have modified the solution to work with SQL Server as below:

create table test (empno int, ename varchar(20), trandate date, amt int);
insert into test values (100, 'Alison'   ,  '21-MAR-1996' ,   45000);
insert into test values (100, 'Alison'   ,  '12-DEC-1978' ,   23000);
insert into test values (100, 'Alison'   ,  '24-OCT-1982' ,   11000);
insert into test values (101, 'Linda'    ,  '15-JAN-1984' ,   16000);
insert into test values (101, 'Linda'    , '30-JUL-1987' ,   17000);
insert into test values (101, 'Linda'    ,  '30-JUL-1987' ,   17000);
insert into test values (102, 'Celia'    ,  '31-DEC-1990' ,   78000);
insert into test values (102, 'Celia'    ,  '17-SEP-1996' ,   21000);
insert into test values (103, 'James'    ,  '21-MAR-1996' ,   45000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);
insert into test values (100, 'Alison'   ,  '21-MAR-1996' ,   45000);
insert into test values (100, 'Alison'   ,  '12-DEC-1978' ,   23000);
insert into test values (100, 'Alison'   ,  '24-OCT-1982' ,   11000);
insert into test values (101, 'Linda'    ,  '15-JAN-1984' ,   16000);
insert into test values (101, 'Linda'    , '30-JUL-1987' ,   17000);
insert into test values (101, 'Linda'    ,  '30-JUL-1987' ,   17000);
insert into test values (102, 'Celia'    ,  '31-DEC-1990' ,   78000);
insert into test values (102, 'Celia'    ,  '17-SEP-1996' ,   21000);
insert into test values (103, 'James'    ,  '21-MAR-1996' ,   45000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);
insert into test values (103, 'James'    , '12-DEC-1978' ,   23000);


select empno, ename,
       ceiling((rank() over (order by empno) +
        count(*) over (partition by empno))/8.) as chunk
,rank() over (order by empno) myRank
, count(*) over (partition by empno)myCnt
from test
order by empno;
/*
empno	ename	chunk	myRank	myCnt
100	Alison	1	1	6
100	Alison	1	1	6
100	Alison	1	1	6
100	Alison	1	1	6
100	Alison	1	1	6
100	Alison	1	1	6
101	Linda	2	7	6
101	Linda	2	7	6
101	Linda	2	7	6
101	Linda	2	7	6
101	Linda	2	7	6
101	Linda	2	7	6
102	Celia	3	13	4
102	Celia	3	13	4
102	Celia	3	13	4
102	Celia	3	13	4
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7
103	James	3	17	7

*/
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s