CODEWITHSUNDEEP
sqlsql-server-2008
cofeee
cofeee

Reputation: 23

SQL Server stored procedure : create increment ID in each occurrence of value

I have a stored procedure and want to supplement the result a new column (NameOcc) that shows my value (Name) occurrence. This is a test table:

| Name |City| Day |
-------------------
| John | Oy |  1  |
| John | Oy |  7  |
|Peter | Oz |  3  |
| Boby | Pi |  9  |
| Boby | Pi |  4  |
| John | Pi |  4  |
| Boby | Pi |  8  |

I want this result:

| Name |City| Day |NameOcc|
---------------------------
| John | Oy |  1  |   1   |
| John | Oy |  7  |   2   |
|Peter | Oz |  3  |   1   |
| Boby | Pi |  9  |   1   |
| Boby | Pi |  4  |   2   |
| John | Pi |  5  |   3   |
| Boby | Pi |  8  |   3   |

Do you have any ideas how to do this?

Upvotes: 1

Views: 282

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460058

You can use a CTE(common-table-expression) + ROW_NUMBER:

WITH CTE AS(
    SELECT n.*, 
           NameOcc = ROW_NUMBER() OVER (PARTITION BY Name, City ORDER BY Name, City, Day)
    FROM dbo.Names n
)
SELECT * FROM CTE

Demo

Here is a more meaningful example with the correct order derived from a primary-key column that you haven't provided. The logic is the same.

Upvotes: 3

Related Questions