Generate SubOrder ID to Orders MySQL

To differentiate between OrderIDs that are identical, it is suggested to include a SubOrderID as an additional field with a serial number suffix. For example, in the event that the OrderID is 100123 and is repeated twice, the SubOrderID values in the additional column should be 1001231 and 1001232.

— Sample dataset
CREATE TABLE tbOrders(
iOrderID INT,
OrderDate DATETIME);

INSERT INTO tbOrders VALUES
(100123, ‘2023-12-01’),
(100123, ‘2023-12-01’),
(100124, ‘2023-12-01’),
(100125, ‘2023-12-01’),
(100126, ‘2023-12-01’),
(100126, ‘2023-12-01’);

Solution:
SELECT iOrderID, CONCAT(iOrderID, ROW_NUMBER() OVER(PARTITION BY iOrderID)) SubOrderID
FROM tbOrders
ORDER BY iOrderID;

Output:
iOrderID SubOrderID
100123 1001231
100123 1001232
100124 1001241
100125 1001251
100126 1001261
100126 1001262