SQL query to populate index field based on groups

Question

Hi everyone,

I'm fairly new to sql and was hoping that someone can help me with an update query. I have a users table with group_id (foreign key), user_id and user_index columns. There are multiple users corresponding to each individual group_id, and user_id is a serial column which goes from 1 to the table size.

I'm looking for a query that will update the user_index column so that, for each group_id, each user will have a unique, sequential index starting with 1. So within group 1 there would be user_index 1,2,3... and within group 2 there would be user_index 1,2,3... and so on. Here is an example to clarify:

Initial state:

user_id | group_id | user_index
1         1          0
2         1          0
3         1          0
4         2          0
5         3          0
6         3          0

Desired state:

user_id | group_id | user_index
1         1          1
2         1          2
3         1          3
4         2          1
5         3          1
6         3          2

I hope that's clear. This would be easy to do in C or C++, but I'm wondering if there's a way to do it in sql.

Answer 1

UPDATE TableName
SET user_index = (SELECT COUNT(1) FROM TableName t2 
   WHERE t2.group_id = TableName.group_id AND t2.user_id <= TableName.user_id)

---

EDIT:
After looking at author's comment I created a test to see is this the right solution. Here's the test:

CREATE TABLE #table (user_id int, group_id int, user_index int)

INSERT INTO #table VALUES (1, 1, 0)
INSERT INTO #table VALUES (2, 1, 0)
INSERT INTO #table VALUES (3, 1, 0)
INSERT INTO #table VALUES (4, 2, 0)
INSERT INTO #table VALUES (5, 3, 0)
INSERT INTO #table VALUES (6, 3, 0)

SELECT * FROM #table

UPDATE #table
SET user_index = (SELECT COUNT(1) FROM #table t2 
WHERE t2.group_id = #table.group_id AND t2.user_id <= #table.user_id)

SELECT * FROM #table

DROP TABLE #table

The output of two selects are exactly the same as in author's two selects - the first as beginning state, and the second as the desired outcome.