Row number over partitions with is only unique if ConsecutiveNumber is non unique

Question

I need to have a row number which is unique for Items with a unique ConsecutiveNumber, however, if the ConsecutiveNumber occurs more than ones the row number should be the same. But I'm struggling to obtain the desired result. Any help would be much appreciated.

The desired result can be seen in the table below targetOutput.

SELECT InvoiceNr,ConsecutiveNumber,ItemNr
       ,ROW_NUMBER() OVER (PARTITION BY ItemNr, InvoiceNr
                           ORDER BY  ItemNr, InvoiceNr) as currentOutput
FROM someTable

InvoiceNr   ConsecutiveNumber   ItemNr          currentOutput   targetOutput    
20001222    118                 h46f4-22-05     1               1
20001222    343                 h46f4-22-05     2               2
20001222    1243                h46f4-22-05     3               3
20001222    1991                h46f4-22-05     4               4
20001222    461                 n8-91           1               1
20001222    1495                n8-91           2               2
20001222    1495                n8-91           3               2
20001222    1495                n8-91           4               2
20001222    1844                pibb53-3-x      1               1
20001222    1844                pibb53-3-x      2               1
20001222    1844                pibb53-3-x      3               1

Answer 1

You want a number that honors ties and that increasing sequentially without gaps.

You are describing dense_rank(). I think that you want:

DENSE_RANK() OVER (
    PARTITION BY ItemNr, InvoiceNr 
    ORDER BY ConsecutiveNumber
) as currentOutput

Note that it usually makes little or no sense to have the same columns in the PARTITION BY and ORDER BY clauses. The former defines the groups of rows, while the latter defines the ordering of rows within groups. Using ROW_NUMBER() as you did actually ended up with undefined rows indexes within the groups (numbers could have been assigned in any order within groups).