How to order table after SELECT that generates table-CodePudding

Basically I have a loop in which I change the Number of each value of my table, in the loop I have a variable in order to increase the number of the value, lets call it @IdTop where in each iteration of the loop it increases by 1. So I made the loop and now to change each value by order (each value has a sequential number) I made a select top x from table and then select top 1 from that select. So what I want is to order the table that I get from this SELECT TOP(@IdTop) * FROM tableX WHERE Constraint = 55510 by ID DESC and then Select the top 1 from it. This is what I tried but it isn't doing what I want:

Select TOP(1) T.ID FROM ((SELECT TOP(3) * FROM tableX WHERE Constraint = 55510) ORDER BY ID DESC) AS T

The table has the following values by row for example:

ID	SequentialNumber
123	3
134	4
155	5
156	2
180	1

Example of Iteration:

Iteration 1: @IdTop = 1 Where the ID is 123 the SequentialNumber changes to 1

Iteration 2: @IdTop = 2 Where the ID is 134 the SequentialNumber changes to 2

...

Iteration 5: @IdTop = 5 Where the ID is 180 the SequentialNumber changes to 5

This is the loop that I made:

DECLARE @IdTop int = 1
WHILE @IdTop < (Select MAX(SequentialNumber) From OriginalTable WHERE Constraint = 55510) 
BEGIN
    UPDATE OriginalTable SET SequentialNumber = @IdTop WHERE ID = 
    (Select TOP(1) T.ID FROM (SELECT TOP(@IdTop) * FROM OriginalTable WHERE Constraint = 55510 ORDER BY ID DESC) AS T)
    SET @IdTop = @IdTop   1
END

Table after the loop:

ID	SequentialNumber
123	1
134	2
155	3
156	4
180	5

CodePudding user response：

I think that this:

(Select TOP(1) T.ID 
 FROM (SELECT TOP(@IdTop) * 
       FROM OriginalTable 
       WHERE Constraint = 55510 
       ORDER BY ID DESC
      ) AS T
)

is a difficult way of writing:

SELECT MAX(T.ID)
FROM OriginalTable
WHERE constraint = 55510
ORDER BY ID DESC

The result can also be reached by doing:

UPDATE OriginalTable
SET ID = t.R
FROM (SELECT 
   id, 
   row_number() over (order by id) as R
 FROM OriginalTable
 WHERE Constraint = 55510 
) AS T
WHERE T.ID = OriginalTable.ID

CodePudding user response：

I would recommend to use a Common Table Expression aka CTE instead, like this:

DECLARE @tbl TABLE(ID int, SeqNr int)

INSERT INTO @tbl VALUES (123, 3),(134, 4),(155, 5),(156, 2),(180, 1)

SELECT * FROM @tbl ORDER BY ID

;WITH MyFirstCTE AS
    (SELECT ID AS ROW_ID, ROW_NUMBER() OVER(ORDER BY ID ASC) AS ROW_NUM FROM @tbl)
UPDATE @tbl
SET    SeqNr = ROW_NUM
FROM   MyFirstCTE
WHERE  ID = ROW_ID

SELECT * FROM @tbl ORDER BY ID

Output from first SELECT:

ID          SeqNr
----------- -----------
123         3
134         4
155         5
156         2
180         1

Output from second SELECT:

ID          SeqNr
----------- -----------
123         1
134         2
155         3
156         4
180         5