SQL Script to Auto Increment Depending on Column Value

Question

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?

Which version of SQL?

sam yi
– sam yi

2013-12-04 03:47:28 +00:00
Commented Dec 4, 2013 at 3:47 — sam yi
– sam yi, Commented Dec 4, 2013 at 3:47

Mike Perrenoud · Accepted Answer · 2013-12-04 00:16:44Z

5

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

answered Dec 4, 2013 at 0:16

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Script to Auto Increment Depending on Column Value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related