How to get the Sequence number without using temp table

Here is my problem:
I have a table with following columns:
PersonID FirstName LastName
102 John Ben
103 Josh Parker
104 Mark Ben
Now if I type SELECT * FROM Person WHERE LastName = 'Ben' these two records will be displayed
PersonID FirstName LastName
102 John Ben
104 Mark Ben
But I want this to return with one additional Sequence column like this:
New Column PersonID FirstName LastName
1 102 John Ben
2 104 Mark Ben
How can I add this so called "New Column" ?


Here's one way:
SELECT
COUNT(*) AS [New Column],
P1.PersonId,
P1.FirstName,
P1.LastName
FROM Person AS P1
JOIN Person AS P2 ON
P2.LastName = P1.LastName
AND P2.PersonId <= P1.PersonId
WHERE P1.LastName = 'Ben'
GROUP BY
P1.PersonId,
P1.FirstName,
P1.LastName
|||You are AWESOME !