CODEWITHSUNDEEP
sqlsql-server
mcspiral
mcspiral

Reputation: 157

SQL - Update column for different rows

After merging tables, I got the following result

| id | count | some_id |
| 0  |  0    |  1      |
| 1  |  1    |  1      |
| 2  |  2    |  1      |
| 3  |  7    |  1      |
| 4  |  12   |  1      |
| 5  |  1    |  2      |
| 6  |  2    |  2      |
| 7  |  5    |  2      |
and so on...

Per some_id, I need to change the count to the correct counting sequence

The result I would need is

| id | count | some_id |
| 0  |  0    |  1      |
| 1  |  1    |  1      |
| 2  |  2    |  1      |
| 3  |  3    |  1      |
| 4  |  4    |  1      |
| 5  |  0    |  2      |
| 6  |  1    |  2      |
| 7  |  2    |  2      |
and so on...

Assuming some_id is 0<some_id<n

Is there a way I can do this? The only thing I'm thinking is using loops but are there any other ways besides that?

Upvotes: 0

Views: 56

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

User ROW_NUMBER()

SELECT
      id,
      -1 + ROW_NUMBER()  OVER (PARTITION BY some_id ORDER BY some_id) as [Count],
      some_id
FROM YourTable

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

You have to use DENSE_RANK to achieve the same:-

SELECT id, DENSE_RANK() OVER (ORDER BY some_id) AS 'Count', some_id
FROM your_table;

Upvotes: 0

Related Questions