Dynamic Identity based on value of other column

Question

I am Creating a table such as student name, class ,roll no

roll no is set as identity,information of student is being saved in on table such as student name,class,roll no

i want such that

Student Name    Class   Roll No
   Snehal         1      1
    Alok          1      2
   Sandeep        1      3
  Swapnil         1      4
   Kunal          1      5
   Amit           1      6
  Nitin           2      1
 Sudhanshu        2      2
  Lokesh          1      7
  Swati           2      3
  Rajesh          1      8

i want that when i insert any person name and enter in class 1 then automatically its roll no should be 9 and when i insert any name and put class 2 then roll no should be 4 and if i enter the person name with class 3 then its roll no should start from 1 as it is 3rd class first record and after that i enter the person name and put class 1 then 10 it want to use identity function in this manner please suggest me as soon as possible me in "die hard condition"

Answer 1

You just need to use SQL Server's ROW_NUMBER() function to create a partition by the Class. This will effectively order the results by the class column and give RollNo a sequential value until the class changes.

SQL Schema Setup:

CREATE TABLE StudentClass
    ([Student Name] varchar(9), [Class] int)
;

INSERT INTO StudentClass
    ([Student Name], [Class])
VALUES
    ('Snehal', 1),
    ('Alok', 1),
    ('Sandeep', 1),
    ('Swapnil', 1),
    ('Kunal', 1),
    ('Amit', 1),
    ('Nitin', 2),
    ('Sudhanshu', 2),
    ('Lokesh', 1),
    ('Swati', 2),
    ('Rajesh', 1)
;

SQL SELECT Using ROW_NUMBER():

SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY Class ORDER BY Class ) AS RollNo
FROM    StudentClass

Output:

Student Name    Class   RollNo
===============================
Snehal              1       1
Alok                1       2
Sandeep             1       3
Swapnil             1       4
Kunal               1       5
Amit                1       6
Lokesh              1       7
Rajesh              1       8
Swati               2       1
Nitin               2       2
Sudhanshu           2       3