Incremented calc column

[metaltree]

I have the following fields:

Column1 Column2
1005 1
1005 2
1005 3
1005 4
1010 1
1010 2
1003 1
1203 2
1203 3
1203 4
...

Is there a way to have Column2 automatically generated, that is, an incremented number that resets to 1 for every changing value in Column1, using only SQL syntax?

Thanks,

 

[r937]

you may need an incrementing id -- see thread220-1415975

r937.com | rudy.ca

 

[metaltree]

Very good thread by PHV, thanks to you both!

Here is how I had it work with a SELECT statement:

id group
1 1005
2 1010
3 1010
4 1203
5 1203
6 1203

SELECT A.id, A.group,(select count(*) from Table AS B where B.group=A.group AND B.id<=A.id) AS [pos]
FROM Table AS A;

id group pos
1 1005 1
2 1010 1
3 1010 2
4 1203 1
5 1203 2
6 1203 3