SQLTeam.com | Weblogs | Forums

To get the number in sequential order


#1

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
Kiran


#2

use row_number()

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

#3

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


#4

try this

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

#5

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


#6
UPDATE T
SET leader_number = new_no
FROM (
	SELECT *,
		new_no = row_number() OVER (
			PARTITION BY group_id ORDER BY leader_number
			)
	FROM member
	) T
WHERE leader_number <> new_no

#7

Thanks..its working..