CODEWITHSUNDEEP
sql-serverpaginationsql-server-2005
Manh Trinh
Manh Trinh

Reputation: 56

Paging in SQL Server problems

I have searched for paging in SQL Server. I found most of the solution look like that

What is the best way to paginate results in SQL Server

But it don't meet my expectation.

Here is my situation:

I work on JasperReport, for that: to export the report I just need pass the any Select query into the template, it will auto generated out the report

EX : I have a select query like this: 

Select * from table A

I don't know any column names in table A. So I can't use 

Select ROW_NUMBER() Over (Order By columsName)

And I also don't want it order by any columns.

Anyone can help me do it?

PS: In Oracle , it have rownum very helpful in this case.

Select * from tableA where rownum > 100 and rownum <200

Paging with Oracle

Upvotes: 1

Views: 965

Answers (5)

Sridhar
Sridhar

Reputation: 1

You can use the below query aswell.

declare @test table(
id int,
value1 varchar(100),
value2 int)

insert into @test values(1,'10/50',50)
insert into @test values(2,'10/60',60)
insert into @test values(3,'10/60',61)
insert into @test values(4,'10/60',10)
insert into @test values(5,'10/60',11)
insert into @test values(6,'10/60',09)

select * 
from ( select row_number() over (order by (select 0)) as rownumber,* from @test )test 
where test.rownumber<=5

Upvotes: 0

Manh Trinh
Manh Trinh

Reputation: 56

Thank for all your help. Because of order by are required when paging in MS SQL Server, so I used ResultSetMetaData to get the Columns name and do paging as well.

Upvotes: 0

Matt Whitfield
Matt Whitfield

Reputation: 6574

You should use ROW_NUMBER with an ORDER BY - because without an ORDER BY there is no determinism in how rows are returned. You can run the same query three times and get the results back in three different orders. Especially if merry-go-round scans come into play.

So unless you want your report to have the possibility of showing the same rows to users on multiple pages, or some rows never on any page, you need to find a way to order the result set to make it deterministic.

Upvotes: 2

Murtuza Kabul
Murtuza Kabul

Reputation: 6524

Check out this link

http://msdn.microsoft.com/en-us/library/ms186734.aspx

SQL Server has similar function ROW_NUMBER. Though it behaves a bit differently.

SQL Server provides no guarantee of row order unless you have have specified a column in order by clause. I would recommend that you give an order by clause that has unique values.

Upvotes: 0

klvoek
klvoek

Reputation: 81

From my opinion, you can use sql query to find out how many columns in a table, and then find out a proper one for ' order by ' to depend on. The script of how to get out columns of an table refer to : How can I get column names from a table in SQL Server?

Upvotes: 0

Related Questions