A challenging query about grouping and order

Greetings !!

I am facing a challenge and hope some one can help me with the query.

I have a school. School have classrooms. Classrooms have students. Classrooms are divided into various sections (Section A, Section B and so on) .Every student is allocated a rollnumber in that section. When a student is transfered from one section to the other, there are gaps in the roll number of other students of the same section. For eg:

Section A

Student 1 Roll no. 1
Student 2 Roll No. 2
Student 3 Roll No 3

Student 4 Roll no 4 and so on.

Now lets say student 2 was transferred to section B, so now there is a gap as roll no 2 is missing in section A

So what should happen is that roll no need to be grouped together based on the section and the rest be readjusted accordingly. So when a student is removed, the roll nos of other students need to be adjusted accordingly. In our case, student 3 will get a roll no of 2, student 4 will have roll no of 3 and so on.

How do I write a query for this. I need to run this as a batch process every fortnight.

Thanks

Suppose if the Roll No is not a foriegn key for other tables then I will agree with your design.

Solution for SQL Server 2005,

Code Snippet

Create Table #data (

[RId] int ,

[Class] int ,

[Section] Char ,

[RollNo] int

);

Insert Into #data Values('1','1','A','1');

Insert Into #data Values('2','1','A','2');

Insert Into #data Values('3','1','A','3');

Insert Into #data Values('4','1','A','4');

Insert Into #data Values('5','1','A','5');

Insert Into #data Values('6','1','A','6');

Insert Into #data Values('7','1','B','1');

Insert Into #data Values('8','1','B','2');

Insert Into #data Values('9','1','B','3');

Insert Into #data Values('10','1','B','4');

Insert Into #data Values('11','1','B','5');

Insert Into #data Values('12','1','B','6');

Insert Into #data Values('13','1','B','7');

Insert Into #data Values('14','1','B','8');

Insert Into #data Values('15','1','B','9');

--Transfer the Student 4 from A section to B section

Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4

--To fill the current gap

;WITH CTE

as

(

Select * , Row_Number() OVER (Partition By Class,Section Order By RollNo) NewRollNo

from #data

)

Update

CTE

Set

RollNo = NewRollNo

Where

RollNo <> NewRollNo

|||

For SQL Server 2000,

Code Snippet

Create Table #data (

[RId] int ,

[Class] int ,

[Section] Char ,

[RollNo] int

);

Insert Into #data Values('1','1','A','1');

Insert Into #data Values('2','1','A','2');

Insert Into #data Values('3','1','A','3');

Insert Into #data Values('4','1','A','4');

Insert Into #data Values('5','1','A','5');

Insert Into #data Values('6','1','A','6');

Insert Into #data Values('7','1','B','1');

Insert Into #data Values('8','1','B','2');

Insert Into #data Values('9','1','B','3');

Insert Into #data Values('10','1','B','4');

Insert Into #data Values('11','1','B','5');

Insert Into #data Values('12','1','B','6');

Insert Into #data Values('13','1','B','7');

Insert Into #data Values('14','1','B','8');

Insert Into #data Values('15','1','B','9');

--Transfer the Student 4 from A section to B section

Update #data Set RollNo=10, Section='B' Where Section='A' And RollNo=4

--To fill the current gap

Update #data

Set

RollNo = (Select Count(*) From #data Sub

Where Sub.Class=#data.Class And Sub.Section=#data.Section And Sub.RollNo<= #data.RollNo)

|||

Thanks Manivannan for the solution. I will try the solution and revert back.

Regards,

Lalit

|||

Hi,

Currently the query is for a single classroom. Could you also suggest me how to extend this query to run for multiple classrooms one after the other.

I hope you help me out. Thanks a ton Manivannan.

|||The above query is capable to handle multiple calssrooms also..