Renumbering a set of rows

I have a table with data which looks something like the following:

# the columns #
url
title
group
id_within_group

http://www.google.com
google
search engine
1

http://www.yahoo.com
yahoo
search engine
2

http://www.bing.com
bing
search engine
3

http://www.facebook.com
facebook
social media
1

http://www.twitter.com
twitter
social media
2

if I do

select * from table1 where group = "search engine"

I will get

http://www.google.com
google
search engine
1

http://www.yahoo.com
yahoo
search engine
2

http://www.bing.com
bing
search engine
3

I would like to be able to delete any of these rows, but somehow get it to renumber the id_within_group.

SO if I delete the second one开发者_JS百科 from the 3 above, it should automatically renumber then leaving me with

http://www.google.com
google
search engine
1

http://www.bing.com
bing
search engine
2

Is this possible?

My suggestion would be to do this in the SELECT if you just need to display a sequential integer.

SELECT   url             ,
         title           ,
         [group]         ,
         id_within_group ,
         ROW_NUMBER() over (partition BY [group] ORDER BY id_within_group) AS sequential_index
FROM     YourTable