I need to sequence a Num_Linea column, but the sequence must be restarted for each ID_PO

Question

Currently the cursor does the sequence but does not restart when the ID_PO is different.

DECLARE @Num_Line as int;
DECLARE @CurNum_Line as int;

SELECT @Num_Line = 1;

DECLARE LINECURSOR CURSOR FOR
    SELECT Num_Linea
    FROM   #temp

OPEN LINECURSOR;

FETCH NEXT FROM LINECURSOR INTO @CurNum_Line

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE #temp
    SET Num_Linea = @Num_Line
    WHERE CURRENT OF LINECURSOR;

    SELECT @Num_Line = @Num_Line + 1;

    FETCH NEXT FROM LINECURSOR INTO @CurNum_Line
END;

CLOSE LINECURSOR;
DEALLOCATE LINECURSOR;

This is the result that I get

ID_PO	Num_Line
51249	1
51249	2
51249	3
51249	4
71086	5
71086	6
71086	7
71108	8
71108	9
71198	10

The result that I need:

ID_PO	Num_Line
51249	1
51249	2
51249	3
51249	4
71086	1
71086	2
71086	3
71108	1
71108	2
71198	1

Answer 1

I found the solution, it was as simple as this:

DECLARE @Num_Line as int;
DECLARE @CurNum_Line as int;
DECLARE @IDPO varchar(6);
DECLARE @LASTPO varchar(6);
select @Num_Line = 0;

DECLARE LINECURSOR CURSOR FOR
    SELECT Num_Linea, ID_PO
    FROM   #temp


OPEN LINECURSOR;

FETCH NEXT FROM LINECURSOR INTO @CurNum_Line, @IDPO
SELECT @LASTPO=@IDPO
WHILE @@FETCH_STATUS = 0
    BEGIN
        
        IF(@LASTPO=@IDPO)
        BEGIN
                SELECT @LASTPO=@IDPO

            SELECT @Num_Line = @Num_Line + 1;

        END
        ELSE
        BEGIN
                SELECT @LASTPO=@IDPO

            SELECT @Num_Line = 1;
        END
        UPDATE #temp
        SET    Num_Linea = @Num_Line
        WHERE  CURRENT OF LINECURSOR;
      FETCH NEXT FROM LINECURSOR INTO @CurNum_Line, @IDPO

    END;

CLOSE LINECURSOR;

DEALLOCATE LINECURSOR;