SQL Script to Auto Increment Depending on Column Value

auto-increment, excel, sql, sql-server

Solution

This is very possible with SQL, here is a SQL Fiddle, and here is the content of that fiddle:

CREATE TABLE a
(
  C1 INT,
  C2 VARCHAR(10)
)

GO

INSERT INTO a VALUES (1, 'A')
INSERT INTO a VALUES (2, 'A')
INSERT INTO a VALUES (3, 'A')
INSERT INTO a VALUES (4, 'B')
INSERT INTO a VALUES (5, 'B')

SELECT C1, C2,
  ROW_NUMBER() OVER (PARTITION BY C2 ORDER BY C1) - 1 AS C3
FROM a

Problem

This may be tricky. I have an `xlsx` file with sets of rows that have one similar column value. The issue is that the client did not give me the correct values for another column (the column does not exist). I need to auto increment a new column based on the value in the other column. When the value of the column changes, the incremented var needs to go back to 0. EX: ``` Current C1 | C2 | 1 | A | 2 | A | 3 | A | 4 | B | 5 | B | Desired C1 | C2 | C3 (NEW) 1 | A | 0 2 | A | 1 3 | A | 2 4 | B | 0 5 | B | 1 ``` Is it possible to do this through SQL? Is there a better way to do this in Excel or Access?

Original source