Assign a unique count to each row in the resulting table of a select statement Let's assume we have a simple database that has the following schema: Article(Id, Body, AuthorId) and Author(Id, Name). Suppose that we want to develop a page where each author will be able to view his own posts only. There is only one requirement:


When the author wants to see his posts, the SQL query should allow him to see a counter for each item, starting at 1.


Example
Here's a simple set up:
(1,"Hi",1), (2,"there",1), (3,"test",2), (4,"foo",1).

By executing some sort of SQL statement: `select count, body where authorid=1; 
we want to get:
(1,"Hi",1), (2,"there",1), (3,"foo",1).

Question
As you can see, the suggested statement wont work because count does not make sense yet. My question is, is there any way to assign a local count for each item  to produce the result above?
You can use ROW_NUMBER, like this:

SELECT 
    ROW_NUMBER()OVER(ORDER BY ID ASC) AS [Count],
    body 
FROM @Article AS A
WHERE authorid=1


Count                body
1                    Hi
2                    there
3                    foo