One more solution for a pagination

[Kurzman]

Let me know what you think about the following code:
DECLARE @MaxIdValue int
DECLARE @MaxSortFieldValue nvarchar(50)

SELECT TOP 1 @MaxIdValue = [id], @MaxSortFieldValue = [SortField]
FROM (
SELECT TOP PageNumber*RowsPerPage [id], [SortField]
FROM MyTable
WHERE (FilterCondition) ORDER BY [SortField], [id]
) T
ORDER BY [SortField] DESC, [id] DESC

SELECT TOP RowsPerPage * FROM MyTable
WHERE ([SortField] >= @MaxSortFieldValue) AND (([id] > @MaxIdValue) OR
([SortField] <> @MaxSortFieldValue)) AND (FilterCondition)
ORDER BY [SortField], [id]

This is a dynamic SQL and it should be easily fixable.
PageNumber, RowsPerPage, FilterCondition and SortField are going to be
the variables and will be based on the user's search
condition/criteria.
-----------------------------
Thanks for you attention.

[Simon Hayes]

"Kurzman" <ma*@virtuman.com> wrote in message
news:d8**************************@posting.google.c om...

Let me know what you think about the following code:
DECLARE @MaxIdValue int
DECLARE @MaxSortFieldValue nvarchar(50)

SELECT TOP 1 @MaxIdValue = [id], @MaxSortFieldValue = [SortField]
FROM (
SELECT TOP PageNumber*RowsPerPage [id], [SortField]
FROM MyTable
WHERE (FilterCondition) ORDER BY [SortField], [id]
) T
ORDER BY [SortField] DESC, [id] DESC

SELECT TOP RowsPerPage * FROM MyTable
WHERE ([SortField] >= @MaxSortFieldValue) AND (([id] > @MaxIdValue) OR
([SortField] <> @MaxSortFieldValue)) AND (FilterCondition)
ORDER BY [SortField], [id]

This is a dynamic SQL and it should be easily fixable.
PageNumber, RowsPerPage, FilterCondition and SortField are going to be
the variables and will be based on the user's search
condition/criteria.
-----------------------------
Thanks for you attention.

What I think is that your syntax is wrong :-)

If you want a pagination solution, then the usual answer is "do it on the
client side" - this article may be useful:

http://www.aspfaq.com/show.asp?id=2120

Simon

[Kurzman]

> What I think is that your syntax is wrong :-)

If you want a pagination solution, then the usual answer is "do it on the
client side" - this article may be useful:

http://www.aspfaq.com/show.asp?id=2120

Simon
The client side is't primary. The bottleneck of pagination is a server
side, especially for a big tables. From this point of view following
code will works slowly. Also it will works correct only if ArtistName
+ '~' + Title is unique.
ArtistName + '~' + Title= @aname + '~' + @title

Following code hasn't this problem:

([SortField] >= @MaxSortFieldValue) AND (([id] > @MaxIdValue) OR
([SortField] <> @MaxSortFieldValue)) AND (FilterCondition)
ORDER BY [SortField], [id]
If index [SortField], [id] exist it works fast.

P.S. Thanks for a link to intresting article.