ROW_NUMBER() OVER (PARTITION BY) gives same row numbers

May 26, 2022

I use SAP HANA database for a project. I tried this SQL query to get values with a row number. But it gives 1 as all rows.

    SELECT ROW_NUMBER() OVER (PARTITION BY "ItemCode") AS "ID", "ItemCode", "CommitedQty", "JobId", "WarehouseID"
    FROM
        (SELECT "ItemCode", SUM("CommitedQty") AS "CommitedQty","JobId", "WarehouseID"
        FROM "Stock" 
        WHERE "BaseEntry" = 10352
        GROUP BY "ItemCode","JobId", "WarehouseID")

This is the result that I get.

Result

Why all row numbers are became 1. How do I solve this issue?

Thank you.

>Solution :

use this :

SELECT ROW_NUMBER() OVER (ORDER BY "ItemCode") AS "ID", "ItemCode", "CommitedQty", "JobId", "WarehouseID"
    FROM
        (SELECT "ItemCode", SUM("CommitedQty") AS "CommitedQty","JobId", "WarehouseID"
        FROM "Stock" 
        WHERE "BaseEntry" = 10352
        GROUP BY "ItemCode","JobId", "WarehouseID")