Pagination AND "order by" play

Question

Pagination AND "order by" play

Bhuvnesh

SSC Guru

Points: 59351
More actions
August 26, 2010 at 4:18 am

#95067

Hi
I have two question.
First , IS there any concept like "pagination" in sql server 2005 ?
Or second , how can i go for second top 5 records like
declare @t table ( id int )
declare @i int
set @i = 1
while ( @i < = 30)
begin
insert into @t select @i
set @i = @i + 1
end
select top 5 * from @t
where id not in ( select top 5 id from @t)
I need other approach.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Row_Number()?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

@gail

i tried but couldnt

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

It does work, I've done it more than once.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

GilaMonster (8/26/2010)
It does work, I've done it more than once.

not working for me declare @t table ( id int )

declare @i int ,@top int

set @i = 1

set @top = 10

while ( @i < = 30)

begin

insert into @t select i

set @i = @i + 1

end

select * , row_number() over ( partition by id order by id ) from @t

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Why both partition by id and order by id? You're saying that you want the row number to start from 1 for each new ID. Is that really what you want?

To do pagination, you'll also need a filter on the row number. Wrap that query (once you've fixed the row number) in a subquery, and filter in the outer query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass