Hi Piyush,
This can be achieved through a combination of a window partition and a case statement. The following statement will provide you with what you need:
CASE WHEN ROW_NUMBER() over (partition by "ORDERID", "POSTINGDATE", "SHIFT"
order by "ORDERID", "POSTINGDATE", "SHIFT") =1 THEN 'Y'
ELSE '' END AS FLAG
For a full working example, copy and paste the following code:
CREATE COLUMN TABLE "TABLE3298883" ("ORDERID" VARCHAR(3),
"POSTINGDATE" VARCHAR(2),
"SHIFT" VARCHAR(2),
"MATERIAL" VARCHAR(2),
"UNIT" VARCHAR(3),
"QUANTITY" INTEGER CS_INT,
"AMOUNT" INTEGER CS_INT);
INSERT INTO "TABLE3298883"
VALUES('001','D1','S1','M1','KG',1005,2001);
INSERT INTO "TABLE3298883"
VALUES('001','D1','S1','M2','KG',3005,4001);
INSERT INTO "TABLE3298883"
VALUES('001','D2','S2','M2','KG',5005,6002);
INSERT INTO "TABLE3298883"
VALUES('002','D3','S2','M3','CSE',7005,8003);
INSERT INTO "TABLE3298883"
VALUES('002','D3','S2','M4','KG',9005,10005);
INSERT INTO "TABLE3298883"
VALUES('002','D4','S3','M5','KG',11005,12005);
INSERT INTO "TABLE3298883"
VALUES('002','D4','S3','M6','KG',14000,15000);
SELECT "ORDERID",
"POSTINGDATE",
"SHIFT",
"MATERIAL",
"UNIT",
"QUANTITY",
"AMOUNT",
CASE WHEN ROW_NUMBER() over (partition by "ORDERID", "POSTINGDATE", "SHIFT"
order by "ORDERID", "POSTINGDATE", "SHIFT") =1 THEN 'Y'
ELSE '' END AS FLAG
FROM "TABLE3298883"
ORDER BY 1,2,3
This gives the following result:
For anybody interested in Window Partitions, the SQL Reference can be found here:
Add comment