SQL: how to add a column that works as a composite key with another column

I have an SQL table that looks like this:

    ID | Category
    1  | A
    2  | A
    3  | B
    4  | C
    5  | A
    6  | C
    7  | B
    8  | A

And I want to add a column that works as an inner ID for each Category such that Category and the new column could work as a composite key. The new column should look like this:

    ID | Category | Inner ID
    1  | A        | 1
    2  | A        | 2
    3  | B        | 1
    4  | C        | 1
    5  | A        | 3
    6  | C        | 2
    7  | B        | 2
    8  | A        | 4 

How can I write a query to achieve that?

Answer

You need to give a sequential order in partitions based Category and ordered by ID. This can be achieved using the row_number() window function.

SELECT 
    *,
    ROW_NUMBER() OVER(PARTITION BY Category ORDER BY ID) as [Inner ID]
FROM 
    Table
ORDER BY
    ID