Update Lowest RN

Question

How can I update ONLY the lowest value of rn in my sample DDL below?

Declare @@policedata Table
(
    ordernumber varchar(100)
    ,sc varchar(50)
    ,rn int
)

Insert Into @@PoliceData Values
('abc123', NULL, 1), ('abc123', NULL, 2), ('abc123', NULL, 3)
,('lmn123', NULL, 1), ('lmn123', NULL, 2)

Declare @@supdata Table
(
    ordernumber varchar(100)
    ,sc varchar(50)
    ,sc1 varchar(50)
    ,odate datetime2
)

Insert Into @@supdata Values
('abc123', '10', '0', '2020-01-20 10:31:58.9370000')
,('lmn123', '0', '40', '2020-01-20 10:31:58.9370000')

UPDATE pd
SET pd.sc = sd.sscc
FROM @@policedata
JOIN (
        Select
        ordernumber
        ,SUM(Coalesce(case when CAST(sc As Decimal(16,4)) = 0 THEN Cast(sc1 As Decimal(16,4)) End, Cast(sc1 As Decimal(16,4))) As sscc
        FROM @@supdata 
        WHERE CAST(odate As Date) BETWEEN '2020-01-01' AND '2020-01-30'
        GROUP BY ordernumber) sd
ON pd.OrderNumber = sd.ordernumber
--this is where i'm not sure what to write
WHERE MIN(pd.rn)

MS SQL Server 2016

Answer 1

Try this:

UPDATE p
SET p.sc = COALESCE(NULLIF(s.sc, 0), NULLIF(s.sc1, 0))
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ordernumber ORDER BY rn ASC) AS rn_by_ordernumber
    FROM @@policedata 
) AS p
INNER JOIN @@supdata AS s ON p.ordernumber = s.ordernumber
WHERE p.rn_by_ordernumber = 1;

SELECT * FROM @@policedata;

Answer 2

Maybe this. (I simplified the SUM in the derived table, because it did not compile, and it seemed unnecessarily complex.) I'm assuming that rn is per ordernumber.

; WITH CTE_policedata AS (
         SELECT sc, ordernumber, 
                rowno = row_number() OVER(PARTITION BY ordernumber ORDER BY rn)
         FROM   @@policedata
     )
     UPDATE pd
     SET pd.sc = sd.sscc
     FROM CTE_policedata  pd
     JOIN (
             Select
             ordernumber, SUM(Cast(sc1 As Decimal(16,4))) As sscc
             FROM @@supdata 
             WHERE CAST(odate As Date) BETWEEN '2020-01-01' AND '2020-01-30'
             GROUP BY ordernumber) sd
     ON pd.ordernumber = sd.ordernumber
   WHERE pd.rowno = 1

`