MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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_01", "2020-01-01", "200", "0"),
("Item_01", "Order_01", "2020-01-18", "0", "50"),
("Item_01", "Order_02", "2020-02-15", "400", "0"),
("Item_01", "Order_02", "2020-02-23", "0", "120"),
("Item_01", "Order_03", "2020-02-21", "300", "0"),
("Item_01", "Order_03", "2020-02-21", "0", "180"),

("Item_02", "Order_01", "2020-02-02", "500", "0"),
("Item_02", "Order_01", "2020-02-10", "0", "175"),
("Item_02", "Order_02", "2020-02-03", "900", "0"),
("Item_02", "Order_02", "2020-03-18", "0", "620"),

("Item_03", "Order_01", "2020-08-12", "700", "0"),
("Item_03", "Order_01", "2020-08-25", "0", "280");


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


itemID
orderID
event_date
order_volume
shipped_volume
position


Item_03
Order_01
2020-08-25
0
280
1

Item_02
Order_01
2020-02-10
0
175
2

Item_01
Order_01
2020-01-18
0
50
3

Item_03
Order_01
2020-08-12
700
0
4

Item_01
Order_01
2020-01-01
200
0
5

Item_02
Order_01
2020-02-02
500
0
6

Item_02
Order_02
2020-03-18
0
620
1

Item_01
Order_02
2020-02-23
0
120
2

Item_02
Order_02
2020-02-03
900
0
3

Item_01
Order_02
2020-02-15
400
0
4

Item_01
Order_03
2020-02-21
0
180
1

Item_01
Order_03
2020-02-21
300
0
2


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


itemID
orderID
sum(order_volume)
ShippedVolume
position


Item_03
Order_01
700
280
1

Item_02
Order_02
900
620
1

Item_02
Order_01
500
175
2

Item_01
Order_03
300
180
1

Item_01
Order_02
400
120
2

Item_01
Order_01
200
50
3

itemID	orderID	event_date	order_volume	shipped_volume	position
Item_03	Order_01	2020-08-25	0	280	1
Item_02	Order_01	2020-02-10	0	175	2
Item_01	Order_01	2020-01-18	0	50	3
Item_03	Order_01	2020-08-12	700	0	4
Item_01	Order_01	2020-01-01	200	0	5
Item_02	Order_01	2020-02-02	500	0	6
Item_02	Order_02	2020-03-18	0	620	1
Item_01	Order_02	2020-02-23	0	120	2
Item_02	Order_02	2020-02-03	900	0	3
Item_01	Order_02	2020-02-15	400	0	4
Item_01	Order_03	2020-02-21	0	180	1
Item_01	Order_03	2020-02-21	300	0	2