Q:

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 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?

A:

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

