Create a descending list of orders per item and display the ranking position in seperate column

DB-Fiddle

CREATE TABLE operations (
    id int auto_increment primary key,
    orderID VARCHAR(255),
    itemID VARCHAR(255),
    event_date DATE,
    order_volume INT,
    shipped_volume INT
);

INSERT INTO operations
(itemID, orderID, event_date, order_volume, shipped_volume
)
VALUES 
("Item_01", "Order_XYZ", "2020-05-01", "600", "0"),
("Item_01", "Order_XYZ", "2020-05-18", "0", "315"),
("Item_01", "Order_MTE", "2020-08-15", "400", "0"),
("Item_01", "Order_MTE", "2020-08-23", "0", "120"),
("Item_01", "Order_OBD", "2020-08-21", "500", "0"),
("Item_01", "Order_OBD", "2020-11-17", "0", "380"),

("Item_02", "Order_TLP", "2020-02-02", "500", "0"),
("Item_02", "Order_TLP", "2020-02-10", "0", "175"),
("Item_02", "Order_SNE", "2020-02-03", "900", "0"),
("Item_02", "Order_SNE", "2020-03-18", "0", "620"),
("Item_02", "Order_ADF", "2020-03-27", "100", "0"),
("Item_02", "Order_ADF", "2020-03-28", "0", "30"),
("Item_02", "Order_ZGO", "2020-04-15", "375", "0"),
("Item_02", "Order_ZGO", "2020-04-17", "0", "120"),

("Item_03", "Order_BBI", "2020-03-12", "700", "0"),
("Item_03", "Order_BBI", "2020-10-25", "0", "280"),

("Item_04", "Order_DXR", "2020-12-09", "260", "0"),
("Item_04", "Order_DXR", "2020-12-15", "0", "110");

Expected Result:

        itemID    |   orderID   |    order_volume  |   shipped_volume  |      position
------------------|-------------|------------------|-------------------|--------------------
        Item_04   |  Order_DXR  |        260       |      110          |         1
------------------|-------------|------------------|-------------------|--------------------
        Item_03   |  Order_BBI  |        700       |      280          |         1
------------------|-------------|------------------|-------------------|--------------------
        Item_02   |  Order_ZGO  |        375       |      120          |         1
        Item_02   |  Order_ADF  |        100       |       30          |         2
        Item_02   |  Order_SNE  |        900       |      620          |         3
        Item_02   |  Order_TLP  |        500       |      175          |         4
------------------|-------------|------------------|-------------------|---------------------
        Item_01   |  Order_OBD  |        500       |      380          |         1
        Item_01   |  Order_MTE  |        400       |      120          |         2
        Item_01   |  Order_XYZ  |        600       |      315          |         3 

---

In the results above I want to create a descending list of all orderIDs per itemID.
Starting from the newest order to the oldest order which is defined by the event_date.
The position of an orderID within a certain itemID should be displayed in column position.

---

I tried to go with this query but could not make it work:

SELECT
itemID,
orderID,
sum(order_volume),
sum(shipped_volume),
ROW_NUMBER() OVER (PARTITION BY orderID ORDER BY event_date DESC) as position
FROM operations
GROUP BY 1,2
ORDER BY itemID DESC, orderID DESC;

What do I need to change to get the expected result?

Answer

You must partition by itemid and order by MAX(event_date) DESC in the ROW_NUMBER() window function:

SELECT itemID, orderID,
       sum(order_volume) order_volume,
       sum(shipped_volume) shipped_volume,
       ROW_NUMBER() OVER (PARTITION BY itemID ORDER BY MAX(event_date) DESC) as position
FROM operations
GROUP BY itemID, orderID
ORDER BY itemID DESC, position;

See the demo.
Results:

> itemID  | orderID   | order_volume | shipped_volume | position
> :------ | :-------- | -----------: | -------------: | -------:
> Item_04 | Order_DXR |          260 |            110 |        1
> Item_03 | Order_BBI |          700 |            280 |        1
> Item_02 | Order_ZGO |          375 |            120 |        1
> Item_02 | Order_ADF |          100 |             30 |        2
> Item_02 | Order_SNE |          900 |            620 |        3
> Item_02 | Order_TLP |          500 |            175 |        4
> Item_01 | Order_OBD |          500 |            380 |        1
> Item_01 | Order_MTE |          400 |            120 |        2
> Item_01 | Order_XYZ |          600 |            315 |        3