SQL Server - Order Table Data Before Updating

2vuwiymt 于 2023-05-28 发布在 SQL Server

关注(0)|答案(1)|浏览(204)

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

sql-server

来源：https://stackoverflow.com/questions/76316374/sql-server-order-table-data-before-updating

1条答案

按热度按时间

yhqotfr81#

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

赞(0）回复(0）举报 2023-05-28

我来回答

SQL Server - Order Table Data Before Updating

1条答案

相关问题

热门标签

最新问答