0

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

  • I thought I had to have the select BatchID because I had the group by clause and I had GO between DECLARE statement and SET .. – StixO Jan 22 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.