x

Paging in SQL Server

Hi,

Please, I'm developing a web application and I have two tables that could be many many records. I need make a query search (stored procedure) that limit the result to 20 records for page.

I think that I must use ROW_NUMBER ou create a temp table, but the fact is that I don´t know why.

Can you help me?

Thanks

Marcos Cruz, São Paulo, Brasil

SELECT s.SetorAtividadeId
,s.SetorAtividade
,rAtividade.RamoAtividadeId
,rAtividade.RamoAtividade
FROM
SetorAtividade As s
LEFT OUTER JOIN
RamoAtividade as r
ON s.SetorAtividadeId = r.SetorAtividadeId
WHERE
(s.SetorAtividade LIKE @param)
ORDER BY
sAtividade.SetorAtividade
,r.RamoAtividade
more ▼

asked Oct 15 '09 at 11:20 PM in Default

Marcos Cruz gravatar image

Marcos Cruz
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Also refer method 4 http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx Here is the code

Declare @t table(item varchar(100), price float)
insert into @t
select 'item1', 20000 union all
select 'item1', 20000 union all
select 'item1', 20700 union all
select 'item2', 57600 union all
select 'item2', 80120 union all
select 'item3', 89760 union all
select 'item3', 87680 union all
select 'item4', 87680 union all
select 'item4', 43220 union all
select 'item4', 43220 Select item,price from
(
select row_number() over(order by item) as row_number, * from @t
) T
where row_number between 1 and 5
more ▼

answered Oct 16 '09 at 04:47 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

I wonder if you want to re-post that here (with a link to your blog too) so that ask.sqlteam has a complete answer? Just a thought ... :)
Oct 16 '09 at 07:06 AM Kristen ♦
I dont understand what you actually meant. Do you think my reply is just a duplicate to other reply? [:)].
Oct 19 '09 at 03:21 AM Madhivanan
Sorry. No, I mean to duplicate the code from your Blog here too - so that there is a complete answer here. I think better for Ask.SQLTeam "completeness", good for Google Spiders, etc.
Oct 19 '09 at 11:10 AM Kristen ♦
Kristen, Now I understand what you meant. Thanks. I will do it [:)]
Oct 20 '09 at 03:13 AM Madhivanan
(comments are locked)
10|1200 characters needed characters left

You can do paging like this in SQL 2005+

SELECT TOP 40 StudentId, StudentName
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY StudentName ASC) as row,
StudentId, StudentName
FROM (
SELECT [Student].[StudentId] AS StudentId, [Student].[StudentName] AS StudentName FROM Student Student
) query
) page
WHERE page.row > 0
ORDER BY StudentName ASC
more ▼

answered Oct 16 '09 at 12:04 AM

craigvn gravatar image

craigvn
23 1 2 2

what is the purpose of page.row > 0 ?
Oct 16 '09 at 12:08 AM Squirrel 1
(comments are locked)
10|1200 characters needed characters left

We talked about paging result sets over here.

more ▼

answered Oct 16 '09 at 04:47 PM

mrdenny gravatar image

mrdenny
908 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x10

asked: Oct 15 '09 at 11:20 PM

Seen: 1078 times

Last Updated: Oct 16 '09 at 10:27 AM