Foreign key value count for each row in a table

Question

I'm trying to generate a table which returns a count for the preceding instances of the foreign key value up to and including the foreign key value on that row. ForeignIDValue is non-nullable. I've tried table variables and common table expressions but it gets long winded and messy. Is there a more elegant and concise way of doing it?

So table A

PrimaryKeyValue ForeignIDValue  ProgressiveForeignIDValueCount
15              42              NULL
16              42              NULL
17              43              NULL
18              42              NULL
19              42              NULL
20              42              NULL
24              42              NULL
26              42              NULL
27              42              NULL
29              42              NULL
30              42              NULL
31              42              NULL
32              42              NULL
35              42              NULL
36              42              NULL
37              42              NULL
38              42              NULL
39              42              NULL
40              44              NULL
41              45              NULL
42              46              NULL
43              45              NULL

Needs to become Table B

PrimaryKeyValue ForeignIDValue  ProgressiveForeignIDValueCount
15              42              1
16              42              2
17              43              1
18              42              3
19              42              4
20              42              5
24              42              6
26              42              7
27              42              8
29              42              9
30              42              10
31              42              11
32              42              12
35              42              13
36              42              14
37              42              15
38              42              16
39              42              17
40              44              1
41              45              1
42              46              1
43              45              2

Does this answer your question? [How to get cumulative sum](https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum) (just replace `SUM` with `COUNT`). — Thom A, Mar 17 '22 at 11:47
@Larnu I did try and adapt that but I couldn't get it to work. Thank you for your input. I'm going to take the easy option and use the option posted by @AaronBertrand! — stonypaul, Mar 17 '22 at 11:59

score 1 · Accepted Answer · answered Mar 17 '22 at 12:00

SELECT PrimaryKeyValue, ForeignIDValue,
  ProgressiveForeignIDValueCount = ROW_NUMBER() OVER
    (PARTITION BY ForeignIDValue ORDER BY PrimaryKeyValue)
FROM dbo.[your table name]
ORDER BY PrimaryKeyValue, ProgressiveForeignIDValueCount;

Example db<>fiddle

Foreign key value count for each row in a table

1 Answers1