SQL Duplicates Query

Question

The table is as follows:

id name
1 tom
2 bob
3 tom
4 tom
5 harry

I am looking for a query that will result in the following:

id name duplicateposition
1 tom 1
2 bob 1
3 tom 2
4 tom 3
5 harry 1

Thus, the duplicate position for the first volume is 1, for the second volume - 2, and for the third volume - 3. The first bob is 1, and the first harry is 1. Is there a simple query for this?

Here is my attempt:

SELECT id, name, count(id) from table

I am a little new to sql, so this is my best shot.

Answer 1

You can do the following. It separates the data by name and assigns a number inside the section. I forgot to note that I did this in SQL Server 2012.

select *, 
  row_number() over (partition by name order by id) as DuplicatePosition
from test
order by id

SQL Fiddle Demo

Answer 2

, (, , , )

select t1.id, t1.name, count(t2.id) duplicateposition
from mytable t1
join mytable t2 
    on t2.name = t1.name
    and t2.id <= t1.id
group by t1.id, t1.name
order by t1.id