Selecting 1st row from each group using SQL

[Former Member]

Hi,

I have a table in HANA,in which I have 7 columns: OrderID, PostingDate, Shift, Material, Unit, Quantity, Amount.

Now for a combination of 1 orderid(001), 1 postingdate (d1) and 1 shift(s1) there can be multiple rows in my table. My requirement is to sort the data of this table in ascending order based on OrderID, PostingDate and Shift and then show a new column say Flag with value as 'Y' only for the 1st row of each group. For example if my data is like:

OrderID	PostingDate	Shift	Material	Unit	Quantity	Amount
001	D1	S1	M1	KG	1005	2001
001	D1	S1	M2	KG	3005	4001
001	D2	S2	M2	KG	5005	6002
002	D3	S2	M3	CSE	7005	8003
002	D3	S2	M4	KG	9005	10005
002	D4	S3	M5	KG	11005	12005
002	D4	S3	M6	KG	14000	15000

I am trying to write a SQL which should return the below mentioned data:

OrderID	PostingDate	Shift	Material	Unit	Quantity	Amount	Flag
001	D1	S1	M1	KG	1005	2001	Y
001	D1	S1	M2	KG	3005	4001
001	D2	S2	M2	KG	5005	6002	Y
002	D3	S2	M3	CSE	7005	8003	Y
002	D3	S2	M4	KG	9005	10005
002	D4	S3	M5	KG	11005	12005	Y
002	D4	S3	M6	KG	14000	15000

Here you can see that the new column "Flag" is initialized only for the 1st row of each group based on OrderID, PostingDate and Shift.

Can you please help me with this requirement?

Regards,

Piyush

[Former Member · Accepted solution]

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:

http://help.sap.com/hana/html/_esql_functions_window.html

[Former Member · Accepted solution]

Hi Gary,

Thanks for you reply. I tried to run, exactly the same SQL provided by you but it gives me Syntax Error near 'over'. If i comment the logic written for Flag, then it works fine.

Do i need to make any change in the setting of my Studio for Windows Partition functions to work? Currently i am running HANA Studio Rev. 41.

Regards,

Piyush

[Former Member · Accepted solution]

Hi Gary,

I just found out from SAP's Whats New Document on SPS05 that windows partitions functions can only be used in SPS05 or above.

Regards,

Piyush

[Former Member · Accepted solution]

Hi Piyush,

Yes, I should've mentioned that this feature is only available from SPS05. I can't think of an elegant way of achieving your objective with Rev.41. Sorry.

Regards,

Gary

[Former Member · Accepted solution]

Hi Garry,

Your input was useful. I will implement the same when we upgrade to SPS05. Thanks for your time.

Regards,

Piyush