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"

after looking at your edit, why do you need to store this value? you can have a unique id per row with a standard ID column, and you can get the roll no per class when you return data. You should perhaps just consider a composite key made up of student ID column and class Id, which will be a unique combination. what is the actual problem you are trying to solve? — Tanner, Nov 28 '14 at 12:05
Actually i am designing a application in which i will be displaying customer information to our client then from our client end they will be entering the class of customer according to there information and there can be a multiple class. so my requirement is that i am having two columns class and serial_No_InClass so i want whenever a existing class is added then the identity should be increment by 1 and if a new class is entered the its identity should start from 1 and after that a existing class is entered again then the number should be grater than the last identity number of that record — Kunal Choudhary, Nov 28 '14 at 12:19

score 0 · Accepted Answer · answered Nov 28 '14 at 10:55

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

Dynamic Identity based on value of other column

1 Answers1