Skip to Content
avatar image
Former Member

Selecting 1st row from each group using SQL

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Jan 26, 2013 at 05:35 PM

    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

    Add comment
    10|10000 characters needed characters exceeded