0

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?

mcspiral
  • 147
  • 1
  • 10
  • Use `Row_Number()` to increment your `count` column - Partitioning by your `some_id` column and ordering by your `Id` column http://stackoverflow.com/a/534280/1693085 – John Bustos Nov 03 '15 at 20:18
  • 1
    You should rewrite the question with more information on what your are trying to accomplish. Consider including how you you generate your first result set. – bsivel Nov 03 '15 at 20:20

2 Answers2

1

User ROW_NUMBER()

SELECT
      id,
      -1 + ROW_NUMBER()  OVER (PARTITION BY some_id ORDER BY some_id) as [Count],
      some_id
FROM YourTable
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
0

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;
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40