Update row only with highest custom priority for all combination of column values

Question

I have a table below (Input table) and need to update is_latest to true based on priority of status for all combiantion of orderid , merchantId and uniqueId Priorities are:

RETURNED  - 1 
CANCELLED - 2
SHIPPED -   3
ORDDERED -  4

Input

OrderId |MerchantId | uniqueId | status  | is_latest
O1        M1           U1        ORDERED   F
O2        M2           U2        ORDERED   F
O1        M1           U1        SHIPPED   F
O2        M2           U2        SHIPPED   F
O2        M2           U2        CANCELLED F
O3        M3           U3        ORDERED   F

Result should be:

OrderId |MerchantId | uniqueId | status   | is_latest
O1       M1           U1        ORDERED     F
O2       M2           U2        ORDERED     F
O1       M1           U1        SHIPPED     T
O2       M2           U2        SHIPPED     F
O2       M2           U2        CANCELLED   T
O3       M3           U3        ORDERED     T

I saw few examples but none of them were for updating all the combination of columns

Thanks in advance

----------I tried with following Query -----------------------

   WITH cte2 AS
(
   SELECT OrderId , merchantId , uniqueId,
         ROW_NUMBER() OVER (PARTITION BY OrderId , merchantId , iniqueId ORDER BY
         CASE order_status 
           WHEN 'ORDERED' THEN 4
           WHEN 'SHIPPED' THEN 3
           WHEN 'CANCELLED' THEN 2
           WHEN 'RETURNED' THEN 1
           ELSE 5
         END 
         ) AS rn
   FROM table1
) 
Update rdyip 
SET rdyip.is_latest = 'T'
            FROM table rdyip
             inner JOIN 
            cte2 
            on 
            rdyip.OrderId = cte2.OrderId and 
            rdyip.MerchantId = cte2.MerchantId and 
            rdyip.uniqueId = cte2.uniqueId 
            where cte2.rn=1

Its updating all the rows as is_latest= 'T'

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Apr 20 '21 at 08:16
That example is not having priority related column. For my example its based on priority of status column — Sonali, Apr 20 '21 at 08:23
But the idea is identical, @Sonali. You are just using a `CASE` expression to define the order in the `ORDER BY` clause. — Thom A, Apr 20 '21 at 08:25
I tried and got idea on CTE but when updating its updating all the rows. I cannot paste what I have tried but I created cte with row number referring to example same as what @larnu suggested and then did inner join with same table to update is_latest but its updating all the rows. — Sonali, Apr 20 '21 at 09:25
Ok I will paste it in answer and show what is tried. I said I cannot paste coz query was long to appear in comment. — Sonali, Apr 20 '21 at 10:18

score 0 · Answer 1 · answered Apr 20 '21 at 09:37

You can use an analytical function, something like this:

SELECT
    a.OrderId
   ,a.MerchantId
   ,a.uniqueId
   ,a.status
   ,IIF(ROW_NUMBER() OVER (PARTITION BY a.OrderId, a.MerchantId, a.uniqueId ORDER BY n.ordinal) = 1, 'T', 'F')
FROM (VALUES

('O1', 'M1', 'U1', 'ORDERED', 'F')
, ('O2', 'M2', 'U2', 'ORDERED', 'F')
, ('O1', 'M1', 'U1', 'SHIPPED', 'F')
, ('O2', 'M2', 'U2', 'SHIPPED', 'F')
, ('O2', 'M2', 'U2', 'CANCELLED', 'F')
, ('O3', 'M3', 'U3', 'ORDERED', 'F')
) a (OrderId, MerchantId, uniqueId, status, is_latest)
INNER JOIN (VALUES
('RETURNED', 1)
, ('CANCELLED', 2)
, ('SHIPPED', 3)
, ('ORDERED', 4)

) n (status, ordinal)
    ON n.status = a.status

I simply join the possible states to get the ordinal value for each state, then I use ROW_NUMBER to sort rows by ordinal within each unique combination.

The lowest number ordinal will be row number 1, so if the row_number is 1, it is the lates, else it is not.

score 0 · Answer 2 · answered Apr 20 '21 at 11:22

It worked just needed one more condition in where clause

     WITH cte2 AS
(
   SELECT OrderId , merchantId , uniqueId, status
         ROW_NUMBER() OVER (PARTITION BY OrderId , merchantId , iniqueId ORDER BY
         CASE status 
           WHEN 'ORDERED' THEN 4
           WHEN 'SHIPPED' THEN 3
           WHEN 'CANCELLED' THEN 2
           WHEN 'RETURNED' THEN 1
           ELSE 5
         END 
         ) AS rn
   FROM table1
) 
Update rdyip 
SET rdyip.is_latest = 'T'
            FROM table rdyip
             inner JOIN 
            cte2 
            on 
            rdyip.OrderId = cte2.OrderId and 
            rdyip.MerchantId = cte2.MerchantId and 
            rdyip.uniqueId = cte2.uniqueId 
            where cte2.rn=1 and rdyip.status=cte2.status

@Lamu https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group this helped , Thanks — Sonali, Apr 20 '21 at 11:23

score 0 · Accepted Answer · answered Apr 20 '21 at 11:48

First, you don't need a JOIN. SQL Server supports updatable CTEs.

Second, you seem to want only the most recent row per merchant. Your PARTITION BY has too many columns.

So:

WITH toupdate as (
      SELECT t1.*,
             ROW_NUMBER() OVER (PARTITION BY merchantId
                                ORDER BY (CASE order_status 
                                              WHEN 'ORDERED' THEN 4
                                              WHEN 'SHIPPED' THEN 3
                                              WHEN 'CANCELLED' THEN 2
                                              WHEN 'RETURNED' THEN 1
                                              ELSE 5
                                          END) 
                               ) AS seqnum
      FROM table1
     ) 
UPDATE toupdate 
    SET is_latest = 'T'
    WHERE seqnum = 1;

If you want to set the other rows to 'F' at the same time, you can use:

UPDATE toupdate 
    SET is_latest = (CASE WHEN seqnum = 1 THEN 'T' ELSE 'F' END);

Update row only with highest custom priority for all combination of column values

3 Answers3