SQLTeam.com | Weblogs | Forums

To get the number in sequential order


Hi All
I have a scenario, that in my member table am storing the group_id,member_id,leader_number.

for example there are 15 members in group-1 the leader number ,vl be assigned 1,2,3...15 to the members of that group.. when ever any member or members r deleted, again I have to reorder the leader number sequentially..

Thanks & Regards


use row_number()

row_number() over ( partition by group_id order by member_id )


when the leader number is deleted , for example 1,2,3,4,5,6...when 3 is deleted 4 should come to 3's position vise versa...can any one help me in solving this


try this

row_number() over ( partition by group_id order by leader_number )


I have to update the new values into the table, replacing the old value when ever there is a change or delet in the leader number

SET leader_number = new_no
		new_no = row_number() OVER (
			PARTITION BY group_id ORDER BY leader_number
	FROM member
	) T
WHERE leader_number <> new_no


Thanks..its working..