sql server - Generate rollno for classes & sections -

sql server - Generate rollno for classes & sections -

table: in sqlserver2008

create table studentreg(id int indentity, rollno int identity, name varchar(5), name varchar(50), class varchar(3) identity, sec varchar(3) identity)

problem: id rollno name class sec 1 101 john 2 101 smith b 3 101 michael ii 4 102 johnson

question:

i want auto generate rollno without entering (i mean internally). know possible identity. want generate rollno class i, sec (starting 101 & increment 1) , 1 time again class i, sec b (again start 101 , increment 1). other classes & sec. can improve understand above diagram.

try this..

select row_number() over(partition class, sec order name) + 100 roll_no, id, name, class, sec tablename

sql-server