Pagination query with MSSQL Server

Hii Richard,

thanks for ur quick reply. I am using java and jsp technology and the pagination is done on sql query level. In the mentioned example

in MSSQL :
select * from (select top 20 * from (select top 3020 * from test order by
idtest) as t1 order by idtest DESC) as t2 order by idtest

what is "3020" and if it is the total count, it means for each execution of the query i need to have the total count. Is it?

Please reply.
aks

No.

This is select entries 3000 to 3020.

If you get no results you are beyond the limit.

Or is it 3020 to 3040.

Not too sure to be honest.

select top 20 * from (select top 3020 * from test order by idtest DESC) order by idtest

Might be better.

If you have the following variables ...

RowsPerPage = 20
PageToShow = 150

Then ...

selelct top RowsPerPage from (select top ((PageToShow + 1) * RowsPerPage) * from test t1 order by t1.idtest desc) as t2 order by t2.idtest

Should do the trick. I've not tested this, and you will need to build this up manually. RowsPerPage and PageToShow will need to be inserted manually.

RIchard.

I forget order there so again:
select tmp.* from (select *,identity(int,1,1) as rowno from Test order by Test.date) tmp where tmp.rowno between 20 and 30

This is tested example. I write so fast without testing, it is not good :)

use Northwind
select *,identity(int,1,1) as rowno into #tmp from customers
select * from  #tmp where rowno between 1 and 5

Oh yes. Pagination is a REAL pain. Snapshot of view is very much the best way. But on a table with millions of rows and a user wants to arbitrarily view the data, you are going to have to accept the fact that the data changes.

hi,

thanks for the replies. Well Richard, ur case might be useful. But do i need to care much on order by clause. Actually i want to have a generalised query. If the paging result is dependent on order by clause..???

patrikt, i would like to use the identity(int,1,1) in my mentioned query for oracle as

SELECT * FROM (SELECT tt.*, identity(int,1,1) AS ROWNO   FROM (
 SELECT
 blah...blah..
 FROM table
 WHERE some=some ) tt  WHERE ROWNUM <= 20)  WHERE ROWNO >0

But it seems not working.

P.S: i have almost nill experience with MSSQL Server. So please bear, if i sound wierd.

thanks
aks

My first example is not working on MSSQL. You have to use second one which is storing intermediate result into temporary table. It is work for stored procedure.
If you giveme your requests, I'll try to send you something to start with.

Something general. As school task :)

CREATE PROCEDURE PageN (@pagesize int,@pageno int,@ordercol varchar(30),@table varchar(30))
AS
declare @sql varchar(1000)
declare @n int
set @n=(@pagesize+1)*@pageno
set @sql='select top '+cast(@n as varchar)+' *,identity(int,1,1) as rowno into #tmp from '+@table+' order by '+@ordercol
sp_execuresql @sql
select * from #tmp where rowno between @pagesize*(@pageno-1) and @pagesize*@pageno

As usual it has errors so there is debuged one:

ALTER PROCEDURE PageN (@pagesize int,@pageno int,@ordercol varchar(30),@table varchar(30))
AS
declare @sql nvarchar(1000)
declare @n int
set @n=(@pagesize+1)*@pageno
set @sql='select top '+cast(@n as varchar)+' *,identity(int,0,1) as rowno into #tmp from '+@table+' order by '+@ordercol
EXEC sp_executesql @sql
select * from #tmp where rowno between 1+@pagesize*(@pageno-1) and @pagesize*@pageno
order by rowno

hii,

sorry i m bit late to respond. Patrikt, it is no school task. I generalise the handling by using the scrollable result set in java. MSSQL Server is really dumb in comparison with Oracle.

thanks for you all.
keep up the good work.
aks