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) — Larnu, 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. — Larnu, 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
As per the question guide, please show what you’ve tried and tell us what you found (on this site or elsewhere) and why it didn’t meet your needs. Why do you say you can't paste what you tried? — Dale K, Apr 20 '21 at 09:33
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);

Thanks, This solution Is simpler and clean – Sonali Apr 21 '21 at 11:49 — Sonali, Apr 21 '21 at 11:49

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

3 Answers3