counter inside select statement?

Hi, can you add a counter inside a select statement to get a unique id line of the rows?

In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order.

This is the normal way:

SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @.topicID) ... some more where/order by statements

This is what i want:

DECLARE @.tempCounter bigint
SET @.tempCounter = 0
SELECT @.tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @.topicID)... some more where/order by statements and at the end.. (SELECT @.tempCounter = @.tempCounter + 1)

Anyone know if this can be done?

Use Row Num ( if you are using SQL SErver 2005 ) as

Select ROW_NUMBER() OVER(ORDER BY Some_Field ) AS rownum,
* from Table_Nane

|||

yes i am using MS SQL 2005, it worked for half of the problem, ill display the SP below, what the SP does is to select the posts from a single user, by ordering them first by topicID then by date created.
In order to bring back just what the client need, i incorperated a custom paging system.

DECLARE @.first_idint, @.startRowint
SET @.startRowIndex=(@.startRowIndex- 1)* @.maximumRows+ 1
IF @.startRowIndex= 0
SET @.startRowIndex= 1

SETROWCOUNT @.startRowIndex

SELECT @.first_id=ROW_NUMBER()OVER(ORDERBY forum_answer.topicidASC, forum_answer.idDESC)FROM forum_answerINNERJOIN forum_topicsON forum_topics.id= forum_answer.topicidINNERJOIN forum_boardON forum_board.id= forum_topics.boardidAND forum_board.hidden= 0INNERJOIN forumON forum.id= forum_board.forumidAND forum.hidden= 0RIGHTOUTERJOIN profile_publicinfoON profile_publicinfo.username= forum_answer.usernameWHERE(forum_answer.username= @.UserName)ORDERBY forum_answer.topicidASC, forum_answer.idDESC

SETROWCOUNT @.maximumRows

DECLARE @.tempVarint
SELECT @.tempVar=ROW_NUMBER()OVER(ORDERBY forum_answer.topicidASC, forum_answer.idDESC), forum_answer.topicid, forum_answer.id, forum_answer.username, forum_answer.answer, forum_answer.created, profile_publicinfo.signatureFROM forum_answerINNERJOIN forum_topicsON forum_topics.id= forum_answer.topicidINNERJOIN forum_boardON forum_board.id= forum_topics.boardidAND forum_board.hidden= 0INNERJOIN forumON forum.id= forum_board.forumidAND forum.hidden= 0RIGHTOUTERJOIN profile_publicinfoON profile_publicinfo.username= forum_answer.usernameWHERE(forum_answer.username= @.UserName)AND(@.first_id<= @.tempVar)ORDERBY forum_answer.topicidASC, forum_answer.idDESC

SETROWCOUNT 0

The first thing we do, is to get the id for the first post that should be returned. This works with the ROW_NUMBER method, the second select statement, takes xx number of rows with start from the position it recieves from the first select statement. So if @.first_id is 5 and maximumRows is 5, then the second select statement will only take rows 5->10 from the table.

The problem is the ROW_NUMBER inside the second select statement, sql screams if i put it at the end like (@.first_id <= ROW_NUMBER...

Msg 4108, Level 15, State 1, Procedure Forum_GetUserAnswers, Line 32
Windowed functions can only appear in the SELECT or ORDER BY clauses.

SQL also screams if i put it in the beginning (as shown above), then i get the following error:

Msg 141, Level 15, State 1, Procedure Forum_GetUserAnswers, Line 33
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Thats why i wanted a way to calculate the rows and get a ID according to that. Any idea of how to proceed?