You can use TRY_CAST or TRY_CONVERT

UPDATE #Data
SET Number = TRY_CAST(Number AS float);

Locate the ssms.pkgundef file and edit it as follows

Comment out the following line at the end of the file, and save the file.

• [$RootKey$\Themes\{1ded0138-47ce-435e-84ef-9ec1f439b749}]

Then restart SSMS, and select the theme

...

CursorID is an integer, not a collection.

You need to declare a variable for each field that the cursor returns

SQL does not necessarily evaluate a CTE or sub-query when you think it does.  It will un-nest them if it believes that it is more efficient to do so.

Watch the...

...

5. 6070. As I wrote in the initial post, I would expect the inner query to return these rows, a fairly small number, then give me the top one...

The following code satisfies your requirement

SELECT  t.Testid
      , t.Status
      , t.Startdate
     ...

So, taking a guess at what you are looking for ....

WITH cteData AS (
  SELECT t.Testid
       , t.Status
  ...

I have repeated and loaded  the insert statement

insert into table3 values ('1','Progress', '20210203'), ('1', 'Closed', '20220403'), ('2', 'Closed', '20220703'), ('2', 'Progress', '20220503')

it is showing wrong output