How can I Get Value of Max(col) in a greatest n per grouped by into Declared variable. MS SQL sever

Question

I am looking for how to set the value of @MaxItemSeqNum to the returned result of the column MAX(dbo.[Table1].[Item_Seq_number]) in my select statement. I need this in order to insert the next record with the next sequence for that grouped item. I already have a surrogate primary key that auto-increments and is used to identify all items in the Table. Batch_ID can have duplicates and I group by these and then select the max of itemSeqNum to insert the next sequence itemSeqNum + 1. So if I have 5 records with the same batch_Id I am expecting to get 5 as a result and increment it by 1.

DECLARE @MaxItemSeqNum Int

SELECT Batch_Id,MAX(dbo.[Table1].[Item_Seq_number])
FROM Table1
WHERE Batch_Id = 1
Group By Batch_Id

Answer 1

how to set the value of @MaxItemSeqNum to the returned result

Either

SET @MaxItemSeqNum = ( SELECT MAX([Table1].[Item_Seq_number])
                       FROM Table1
                       WHERE Batch_Id = 1
                       Group By Batch_Id );

or

SELECT @MaxItemSeqNum = MAX([Table1].[Item_Seq_number])
                        FROM Table1
                        WHERE Batch_Id = 1
                        Group By Batch_Id;

fiddle

The last variant is more useful - it allows to assign the values to more than one variable in one statement.