CODEWITHSUNDEEP
sqlsql-server
spcial
spcial

Reputation: 1569

How to count/increment the current number of occurances of a table column in a MS SQL select

I have a table which looks like this:

id | name| fk_something
----------------
0  | 25  | 3
1  | 25  | 2
2  | 23  | 1

and I want to add another column with a number which increments everytime row name occurs, e.g.:

id | name| fk_something| n
--------------------------
0  | 25  | 3           | 1
1  | 25  | 2           | 2
2  | 23  | 1           | 1

I'm not really sure how to achieve this. Using count() I will only get the total number of occurances of name but I want to increment n so that I have a distinct value for each row.

Upvotes: 3

Views: 1343

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

You may try using COUNT as an analytic function:

SELECT
    id,
    name,
    fk_something,
    COUNT(*) OVER (PARTITION BY name ORDER BY id) n
FROM yourTable
ORDER BY
    id;

enter image description here

Demo

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You want row_number() :

select t.*, row_number() over (partition by name order by id) as n
from table t;

Upvotes: 6

Related Questions