optionsix optionsix - 2 months ago 4
SQL Question

Query to loop through one table and insert incrementally by group into another

I need some help with a sql query. I've got two simple tables, one that contains Channels, each with a unique identifier (ChannelID), and another one to many ID (OtherID). The second one will contain a sort order of each channel as it appears in another query. This sorting table is currently empty and my channels table has a couple thousand records.

The intended result is to loop through each ChannelID by OtherID and insert it incrementally into ChannelSorting, starting the SortOrder seed at 1 again for every new "cluster" of channel id's, grouped by OtherID.


ChannelID OtherID
--------- -------
1 1
2 1
3 1
4 1
5 2
6 2

ChannelSorting (with intended result)

ChannelID SortOrder
--------- ---------
1 1
2 2
3 3
4 4
5 1
6 2

vkp vkp

You can use row_number to set the sortorder and insert into the other table.

insert into channelsorting (channelid,sortorder)
select channelid,row_number() over(partition by otherid order by channelid) sortorder
from channels