Paging in SQLServer - 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 2000 Forums

Transact-SQL (2000)

Paging in SQLServer

Author

Topic

markusf
Starting Member

15 Posts

Posted - 2006-11-13 : 07:16:59

I've been reading quite a few articles on how to do Paging in SQLServer.

After doing som tests I found this approach as the the best one:

SELECT *, [any subqueries goes here]
FROM (
SELECT TOP [per_page]
FROM (
SELECT TOP [to_row] f1, f2, f3
FROM table_1
INNER JOIN table_2 ON ...
WHERE ...
ORDER BY x ASC, y ASC, z ASC
) ORDER x DESC, y DESC, z DESC
) ORDER BY x ASC, y ASC, z ASC

---
it is important that we have a per-row-distinct ORDER BY sum, so we get the right selection.

Also all subselects should go on the outer join, so they dont have to be calculated on more than excatly the ones you want to return.

Does anyone have any inputs / comments / better solutions on Paging ?

In advance: Thanks :-)

--
Markus Foss, Norway

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-13 : 07:29:49

here's another one:
http://weblogs.sqlteam.com/jeffs/archive/2004/03.aspx

Go with the flow & have fun! Else fight the flow