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, 2009 at 11:20 PM in Default

avatar image

Marcos Cruz
12 2 2 4

(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, 2009 at 04:47 AM

avatar image

Madhivanan
1.1k 2 5 9

(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, 2009 at 12:04 AM

avatar image

craigvn
23 2 2 4

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

We talked about paging result sets over here.

more ▼

answered Oct 16, 2009 at 04:47 PM

avatar image

mrdenny
928 2 5

(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.

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:

x2016
x9

asked: Oct 15, 2009 at 11:20 PM

Seen: 1409 times

Last Updated: Oct 16, 2009 at 10:27 AM

Copyright 2016 Redgate Software. Privacy Policy