Cloud Stack Ninja

I'm struggling to find the right terminology to explain this issue, but hopefully the following reduced example makes sense.

I have a SQL (MSSQL) 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.



Read more here: https://stackoverflow.com/questions/64414128/add-iteration-number-column-to-sql-select-based-on-same-values-from-two-column

Content Attribution

This content was originally published by Dan at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: