SQL: Add counters in select

Question

Hi,

I have a table which contains names:

Name
----
John Smith
John Smith
Sam Wood
George Wright
John Smith
Sam Wood

I want to create a select statement which shows this:

Name

'John Smith 1'

'John Smith 2'

'Sam Wood 1'

'George Wright 1'

'John Smith 3'

'Sam Wood 2'

In other words, I want to add separate counters to each name. Is there a way to do it without using cursors?

Answer 1

Use ROW_NUMBER():

SELECT Name, ROW_NUMBER() OVER(Partition BY Name ORDER BY Name) as [Rank]
FROM MyTable

Answer 2

Doing:

select name, count(*) as total from table group by name;

will get you something that looks like this:

name         |  total
-------------+------------
John Smith   |  2
-------------+------------
Sam Wood     |  2
-------------+------------
George Wright|  1

This isn't what you really wanted though - ROW_NUMBER(), as ck pointed out, is what you want, but not all databases support it - mysql doesn't, for example. If you're using MySQL, this might help: http://stackoverflow.com/questions/1895110/row-number-in-mysql