The goal is to return the same result,but i to substitute "value" with null
when row_id not in max for product.
current result:
row_id product value product_id
101 product1 10 1
102 product1 10 1
103 product1 10 1
104 product2 20 2
105 product3 20 2
106 product3 30 3
107 product3 30 3
108 product4 40 4
CREATE TABLE tbl26 (row_id int, product VARCHAR2(30), value int, product_id int);
insert into tbl26 (row_id,product,value, product_id)
SELECT 101, 'product1', 10, 1 FROM DUAL UNION ALL
SELECT 102, 'product1', 10, 1 FROM DUAL UNION ALL
SELECT 103, 'product1', 10, 1 FROM DUAL UNION ALL
SELECT 104, 'product2', 20, 2 FROM DUAL UNION ALL
SELECT 105, 'product3', 20, 2 FROM DUAL UNION ALL
SELECT 106, 'product3', 30, 3 FROM DUAL UNION ALL
SELECT 107, 'product3', 30, 3 FROM DUAL UNION ALL
SELECT 108, 'product4', 40, 4 FROM DUAL;
I tried with PARTITION BY,but I didn’t get the desired result.
Expected result:
row_id product value product_id
101 product1 0 1
102 product1 0 1
103 product1 10 1
104 product2 0 2
105 product3 20 2
106 product3 0 3
107 product3 30 3
108 product4 40 4
>Solution :
SELECT row_id
,product
,CASE
WHEN MAX(row_id)
OVER(PARTITION BY product_id) = row_id THEN
value
ELSE
0
END as value
,product_id
FROM tbl26;
ROW_ID PRODUCT VALUE PRODUCT_ID
---------- ------------------------------ ---------- ----------
101 product1 0 1
102 product1 0 1
103 product1 10 1
104 product2 0 2
105 product3 20 2
106 product3 0 3
107 product3 30 3
108 product4 40 4