Add 'iteration number' column to SQL Select based on same values from two columns

Question

I have a SQL (SQL Server) statement SELECT id, animalId, ownerId FROM myTable ORDER BY id which outputs the following data:

id  | animalId | ownerId
------------------------
1   | 123      | 62
2   | 123      | 182
3   | 240      | 27
4   | 2        | 30
5   | 73       | 35
6   | 123      | 62
7   | 108      | 162
8   | 2        | 30
9   | 2        | 30
10  | 73       | 35

What I want to be able to do is add a fourth column dynamically that acts as a counter for each row that has the same values for animalId and ownerId. So the resultant output would be:

id  | animalId | ownerId | iterator
-----------------------------------
1   | 123      | 62      | 1           <-- First instance where animalId is 123 and ownerId is 62
2   | 123      | 182     | 1
3   | 240      | 27      | 1
4   | 2        | 30      | 1
5   | 73       | 35      | 1
6   | 123      | 62      | 2           <-- Second instance where animalId is 123 and ownerId is 62
7   | 108      | 162     | 1
8   | 2        | 30      | 2
9   | 2        | 30      | 3
10  | 73       | 35      | 2

Could anyone advise how to go about this please?

Many thanks.

Answer 1

You can do it with window function ROW_NUMBER():

SELECT id, animalId, ownerId,
       ROW_NUMBER() OVER (PARTITION BY animalId, ownerId ORDER BY id) iterator 
FROM myTable 
ORDER BY id

See the demo.
Results:

> id | animalId | ownerId | iterator
> -: | -------: | ------: | -------:
>  1 |      123 |      62 |        1
>  2 |      123 |     182 |        1
>  3 |      240 |      27 |        1
>  4 |        2 |      30 |        1
>  5 |       73 |      35 |        1
>  6 |      123 |      62 |        2
>  7 |      108 |     162 |        1
>  8 |        2 |      30 |        2
>  9 |        2 |      30 |        3
> 10 |       73 |      35 |        2