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 results that are sorted?

Author  Topic 

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-08-14 : 04:22:49
I am trying to implement recordset paging. I have read a series of articles such as [url]http://www.aspfaqs.com/webtech/042606-1.shtml[/url] and have the basics working. I am hoping someone may be able to help me with the ordering.
These techniques require the UniqueID to be sorted. What if I want users to be able to sort the results by a text field or date? When I try and change the Order By clause the results are thrown out! Can any one help me understand

CREATE PROCEDURE Paged
(
@PageNo int,
@maximumRows int
)
AS

DECLARE @First_id int, @StartRow int
Set @PageNo = (@PageNo * @maximumRows) - (@MaximumRows - 1)

SET ROWCOUNT @PageNo

SELECT @first_id = UniqueID
FROM DirectoryItems
WHERE Itemname='a'
ORDER BY UniqueID

SET ROWCOUNT @maximumRows

SELECT *
FROM DirectoryItems
WHERE UniqueID >= @first_id AND ItemName='a'
ORDER BY UniqueID

SET ROWCOUNT 0
GO

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-09-04 : 03:44:49
*BUMP* Anyone got any ideas?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-04 : 03:53:49
you can sort by date. If you need to sort by text data type, convert it to varchar.

Also refer to http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx


KH

Go to Top of Page
   

- Advertisement -