how can select top 10 records with paging

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how can select top 10 records with paging

Author

Topic

tokue
Starting Member

16 Posts

Posted - 2011-05-12 : 12:39:19

Hi dears,
I have a the follwing table:
ID, insertedBy, item

I want to make paging in my html page with numbers (1,2,3,etc...) for each (insertedBy), if the user click (1) then I will load the first 10 records inserted by this user, this is easy by useing select top (10) from MYTABLE where insertedBy=1, my problem is when clicking 2 or 3 etc on my html page, how can I load the next 10 records

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-12 : 12:53:37

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-12 : 13:43:53

The solution in that link is impractical for huge result sets. I think you want to perform this paging within sql server, right?
There are a lot of server side paging methods - you should search for links and articles here.

But in the mean time here is a very simple example of one way. play with changing the parameters to see the effects:


declare @page int
       ,@pageSize int

--set your paging parameters (passed into stored procedure)
select @page = 2
       ,@pagesize = 10

select d.rn [rownumber], s.name, s.type
from   (select id,name,rn = row_number() over (order by name) from sysobjects) d
join   sysobjects s on s.id = d.id
where  d.rn > (@page-1) * @pagesize 
and    d.rn <= @page * @pagesize

Be One with the Optimizer
TG

tokue
Starting Member

16 Posts

Posted - 2011-05-12 : 14:05:44

Lots of thanks for both of you, your answers solved my problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources