Pagination query - SQL Server Forums

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)

Pagination query

Author

Topic

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-08-21 : 10:20:03

I am trying to put pagination on my web app but I am having trouble with my query below

SELECT DISTINCT TOP $limit tblInvoice.*, tblInvoiceDet.*, tblTrackingOUT.*, tblUsers.uTown
FROM ((tblInvoice INNER JOIN tblInvoiceDet ON tblInvoice.invTrackNo = tblInvoiceDet.invTrackNo) INNER JOIN tblTrackingOUT ON tblInvoice.invTrackNo = tblTrackingOUT.invTrackNo) INNER JOIN tblUsers ON tblInvoiceDet.invWorkshop = tblUsers.uTown
where tblUsers.uTown= '".$town."' AND tblTrackingOUT.invStatus <> 5 AND tblInvoice.invTrackNo NOT IN (SELECT TOP $offset invTrackNo FROM tblInvoice ORDER BY invTrackNo asc) ORDER BY tblInvoice.invTrackNo

Can anyone take a look and tell me where my mistake is possibly provide a better query

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-21 : 10:23:53

you can use the row_number() function.
select row_number() over(order by tblInvoice.invTrackNo) as RN, <other columns>
from ...
where RN between @pageStart and @pageStart + @PageLength

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 10:24:24

or use this

http://weblogs.sqlteam.com/peterl/archive/2008/02/19/Efficient-pagination-for-large-set-of-data.aspx

tkotey
Yak Posting Veteran

75 Posts

Posted - 2008-08-21 : 10:42:31

Thank you all for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources