Row Count Reset?

[robert030975]

HI

I have built table to which I have created an inital composite key

a,b ab
a,b ab
b,c bc
b,c bc

now I was wondering how you would do a count that I can reset on compkey

if I do SELECT row_number() over (order by COMKEY), COMKEY from table


I have ab 1, ab 2, bc 3, bc 4 but really want

ab,1
ab,2
bc,1
bc,2


Please can someone help

Many Thanks

 

[robert030975]

Apologies its been a while since Ive used this

SELECT row_number() over (PARTITION BY COMKEY ORDER BY COMKEY ), COMKEY from table

 

[gmmastros]

You want to use the Partition By keyword. Like this:

SELECT row_number() over ([!]Partition By COMKEY[/!] order by COMKEY), COMKEY from table

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom