Numbering Column when returned from Database

Sometimes we need to return the sequence numbers along with the records. If we are returning 10 rows based on some condition and we need to add a new column which represent each row uniquely we can use the following query.

(Special thanks to Adam Machanic)

SELECT
COUNT(*) AS "New Column",
P1.ArticleID,
P1.Title,
P1.Author
FROM Articles AS P1
JOIN Articles AS P2 ON
P2.Author = P1.Author
AND P2.ArticleID <= P1.ArticleID WHERE P1.Author = 'AzamSharp' GROUP BY P1.ArticleID, P1.Title, P1.Author