How to use row Num() in sql based on duplicate values

In Sql i am having following data on based on Ite and Rev it calculate row num

Folder  FileName    VersionId   DocumentID  DocumentVersion Rev Ite Duplicate
\abc\ abc.sldprt  V1  D00001  1   A   1   1
\abc\ abc.sldprt  V12 D00001  12  A   1   2
\abc\ abc.sldprt  V2  D00001  2   B   1   1
\abc\ abc.sldprt  V3  D00001  3   B   2   2
\abc\ abc.sldprt  V4  D00001  4   B   3   3
\abc\ abc.sldprt  V5  D00001  5   C   1   1
\abc\ abc.sldprt  V6  D00001  6   C   2   2
\abc\ abc.sldprt  V7  D00001  7   C   3   3
\abc\ abc.sldprt  V8  D00001  8   C   4   4
\abc\ abc.sldprt  V9  D00001  9   D   1   1
\abc\ abc.sldprt  V10 D00001  10  D   2   2
\abc\ abc.sldprt  V11 D00001  11  E   1   1

I used below query to generate this table.

select * , 
       ROW_NUMBER() OVER (PARTITION BY Folder, DocumentID, Rev order by Rev, Ite) As "Duplicate"
from Versions v1;

Is there any way to calculate, if Rev & Ite are same then only increase row number lets say 1,2,3 and if Rev and Ite are unique it will assign only 1 so i get duplicate counts based on Rev & Ite.

Desired output looks like

 Folder FileName    VersionId   DocumentID  DocumentVersion Rev Ite Duplicate
    \abc\ abc.sldprt  V1  D00001  1   A   1   1
    \abc\ abc.sldprt  V12 D00001  12  A   1   2
    \abc\ abc.sldprt  V2  D00001  2   B   1   1
    \abc\ abc.sldprt  V3  D00001  3   B   2   1
    \abc\ abc.sldprt  V4  D00001  4   B   3   1
    \abc\ abc.sldprt  V5  D00001  5   C   1   1
    \abc\ abc.sldprt  V6  D00001  6   C   2   1
    \abc\ abc.sldprt  V7  D00001  7   C   3   1
    \abc\ abc.sldprt  V8  D00001  8   C   4   1
    \abc\ abc.sldprt  V9  D00001  9   D   1   1
    \abc\ abc.sldprt  V10 D00001  10  D   2   1
    \abc\ abc.sldprt  V11 D00001  11  E   1   1

So here A 1 is duplicate 2 times

please help or any suggestions.

Answer

I think you want:

select v.* , 
       row_number() over (partition by Folder, DocumentID, Rev, Ite 
                          order by versionId
                         ) As Duplicate
from Versions v;

Advertisement

Answer