Iterate through sub-data records and count up a counter-variable via SQL-CodePudding

I have a master and a detail table that look like this:

Master-Table:

ID	Name
1	Max
2	Kevin
3	Steve

Detail-Table:

ID	MasterID	Position
1	1	0
2	1	0
3	1	0
4	2	0
5	2	0

The Position field has been added to the detail table and I would now like to count this up for each master data record. I solved this with the Entity Framework, but it would be best if this could also be done directly with an SQL statement, as it is a large amount of data and would be faster that way.

So how can you count the sub-data records per master data record over and over again from 1, so that later the detail table would look like this:

ID	MasterID	Position
1	1	1
2	1	2
3	1	3
4	2	1
5	2	2

Thanks in advance for your answers.

Kind regards

Bati_84

CodePudding user response：

You can use ROW_NUMBER for this pretty easily.

select dt.ID
    , dt.MasterID
    , ROW_NUMBER() over(partition by MasterID order by ID)
from MasterTable mt
join DetailTable dt on dt.MasterID = mt.ID

CodePudding user response：

select detail.ID
    , detail.MasterID
    , ROW_NUMBER() over(partition by detail.MasterID order by detail.ID) as Position
from MasterTable master
inner join DetailTable detail on detail.MasterID = master.ID