[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Pagination query with MSSQL Server

Posted on 2005-03-03
13
Medium Priority
?
2,359 Views
Last Modified: 2012-06-27
Hii,

I am using the following for doing the pagination in Oracle.

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

How can i emulate it for MS SQL Server?

Any ideas please.

Thanks
aks
0
Comment
Question by:aks143
  • 6
  • 4
  • 3
13 Comments
 
LVL 40

Accepted Solution

by:
Richard Quadling earned 400 total points
ID: 13448944
Hi aks143,


The best I have for paging with MS SQL is ...

in MySQL :
SELECT * FROM test order by idtest LIMIT 3000, 20

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


This is from my own pager notes. MySQL supports paging, MSSQL does not. I have the comment "You can also use SET ROWCOUNT before the main select". Maybe useful.


set rowcount $limit
select * from Table1 where pk in(
select top $total pk from Table1 order by pk desc )order by pk



Table1: name of the table
PK : Primary Key of Table1
$limit : The number of records you need each time $total : Total number of records in each time

The only thing you need to do is to get the total number of records (select
count(*)) for the first $total value then $n =$total/$limit

//Forloading next page
$total = $total - $limit
//then run the sql statement with new $totalvalue

//For loading page 2
$total = $total - ($limit)*2

//For loading page n
$total = $total - ($limit)*n


1-You can put this code in a stored procedure and pass $limit and $total
values when you calling the stored procedure or you can use it in your php
code

2-You can change the SQL statement to work with your search-query results
instead of Table1


Regards,

Richard Quadling.
0
 

Author Comment

by:aks143
ID: 13449104
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
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 13449221
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.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LVL 40

Expert Comment

by:Richard Quadling
ID: 13449280
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.

0
 
LVL 12

Assisted Solution

by:patrikt
patrikt earned 200 total points
ID: 13449349
RQuadlings double-order solution will work but it can be slow with multiple users and queries.

Pagination is not a simple probleme. First you have to answer question what to do with new/deleted reccords between page queries.
Answers can be:
- Create temporary table for each user at start of first page query. User will have all reccords on correct pages but has to call for refresh if he wants to see new data.
- Use on-the-fly pages (as it is in RQuadlinks) but user can loose-skeep data if it is inserted or deleted from pages he already seen. Or can see different results when refreshing same page.

Generaly it is good to support pagination by some generated row number as this:

select tmp.* from (select *,identity(int,1,1) as rowno from Test) tmp where tmp.rowno between 20 and 30

0
 
LVL 12

Expert Comment

by:patrikt
ID: 13449360
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
0
 
LVL 12

Expert Comment

by:patrikt
ID: 13449419
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
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 13449479
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.

0
 

Author Comment

by:aks143
ID: 13449940
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
0
 
LVL 12

Expert Comment

by:patrikt
ID: 13450177
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.
0
 
LVL 12

Expert Comment

by:patrikt
ID: 13450263
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

0
 
LVL 12

Expert Comment

by:patrikt
ID: 13450324
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
0
 

Author Comment

by:aks143
ID: 13485170
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
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how the fundamental information of how to create a table.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question