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?
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