SQL Server - Order Table Data Before Updating

Question

I need to add a number value to each record in a SQL table. I want to add this number after sorting the data, so that the number (which will be used for ticket entry) will appear in numerical order when viewed by endusers.

I want to sort the table data by [AssignedLocator] and then by [PartNo], before assigning a numerical value in the [TicketNumber] column. I've tried a few different ways, but without success. I most recently tried using a subquery:

DECLARE @TicketNumber INT
SET @TicketNumber = 100000;

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY [AssignedLocator],[PartNo]) AS RowNum
    FROM [Staging].[dbo].[PI_6F_Forms]
)
UPDATE CTE
SET [TicketNumber] = @TicketNumber,
    @TicketNumber = @TicketNumber + 1;

This still incremented the value in the [TicketNumber] field based on the ID num of the table

Answer 1

If I understand correctly, you need to update Ticket Number incrementally using row_number() :

DECLARE @TicketNumber INT;
SET @TicketNumber = 100000;

WITH CTE AS (
    SELECT *, @TicketNumber + ROW_NUMBER() OVER (ORDER BY [AssignedLocator], [PartNo]) AS RowNum
    FROM mytable
)
UPDATE CTE
SET [TicketNumber] = CTE.RowNum

Demo here