CODEWITHSUNDEEP
sqlsql-servert-sql
Missak Boyajian
Missak Boyajian

Reputation: 2245

T-SQL Incremental Update Group Number

I have a table like this. For each student, for each term column change I want to increment the semester number. When the student ID Changes the Semester Number should re begin from 1. My Semester Number is null for now and I wish to update it. Any easy fast solution? I read about Dens_Rank and I am not sure if it is applicable here.

StudentId  | Term   |Course_Number| Semester_Number(Expected)

1             0010     ENG            1
1             0010     AGR            1
1             0020     MAT            2
1             0020     ...            2
1             0110     ...            3
1             0110     ...            3
2             0010     ENG            1
2             0010     MAT            1     
2             0020     PHY            2
3             0010     MAT            1
3             ...
3

Upvotes: 1

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If I understand correctly, you can do:

select t.*,
       dense_rank() over (order by studentid, term) as semester_number
from t;

I have improved my undertanding:

select t.*,
       dense_rank() over (partition by studentid order by term) as semester_number
from t;

Upvotes: 1

Related Questions