Paging query results...or however you say this

[StrangerInBeijing]

I got a table with thousands of news articles.
Need a query that return a certain number of articles, for a specific day, and then display only a certain number at a time.
On the page I guess I will have "Next, Previous, First, Last" links...

So I guess the query will go like "Give me all the articles for this day, from number X up to number "X + 20"

not sure how to start with this...any ideas welcome to push me off

[StrangerInBeijing]

HMMM...seems it might not be so hard. by setting the rowcount, i can limit the number of records returned by the query...but how to I say "start at row number x" ?

SET rowcount 10
SELECT ArticleID, Title
FROM tblArticles 
WHERE PublishDate = '2004-06-02 00:00:00.000' and lang = 1
SET ROWCOUNT 0

[StrangerInBeijing]

this seems to work fine....any critic?

-- the "starting values". In our case, we need two:
DECLARE 	@startingID 	INT; 
-- returns resutls from row @a to row @b:
DECLARE 	@a 		INT; 
DECLARE 	@b 		INT; 
SET @a = 1 -- start at row 2
SET @b = 100 -- end at row 5
-- get the starting date and starting ID to return results:
SET rowcount @a 
SELECT @startingID = ArticleID 
FROM tblArticles
WHERE PublishDate = '2004-06-04 00:00:00.000' and lang = 1
ORDER BY ArticleID ASC 
-- find out how many rows to return, and set the rowcount:
SET @b = 1 + @b - @a 
SET rowcount @b 
 -- now return the results:
SELECT * FROM tblArticles
WHERE 
PublishDate = '2004-06-04 00:00:00.000' AND lang = 1 AND ArticleID >= @StartingID
ORDER BY PublishDate ASC,ArticleID ASC 
-- clean up:
SET rowcount 0

let me convert this to a sproc
now for the page itself.
hmmm....must know before hand how many articles...guess another query needed
how to keep track of what page we are at, and so on, without running this query again and again (database design bad..not done by me..not very optimized)