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.
Advertisement
Answer
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