Increment row numbers for groups mySQL [duplicate]

I have 3 tables:

Purchase Order

PO ID	PO Requestor
PO123	John Snow
PO567	Heather

Purchase Order Line Item:

PO Line Item ID	PO Line Item	PO ID	Ordered Quantity
1234	Bike	PO123	3
1235	Laptop	PO567	2
1236	BMW	PO123	1

And Receipt:

Receipt Line ID	PO Line Item ID	Received Quantity	Received Date
R123	1234	1	01-Dec-20
R567	1236	1	15-Dec-20
R567	1234	2	15-Dec-20
R987	1235	2	20-Dec-20

I want to join 3 tables into 1 table as below, which is not a challenging task:

PO ID	PO Line Item No	PO Line Item Name	Ordered Quantity	Receipt Line ID	Received Quantity	Received Date	Requestor
PO123	1	Bike	3	R123	1	01-Dec-20	John Snow
PO123	1	Bike	3	R567	2	15-Dec-20	John Snow
PO123	2	BMW	1	R567	1	15-Dec-20	John Snow
PO567	1	Laptop	2	R987	2	20-Dec-20	Heather

In the final table, I want to add a new column: PO Line Item No. Basically, if the group PO ID is the same, it will assign an increment value for each PO Line Item ID/Name. And please note that because 1 PO Line Item can be broken into multiple receipt dates and lines, so the final table may contain multiple lines for the same PO Line Item. In that case, the PO Line Item No should show the same value for those records in the final table

For example, PO123 has 2 PO Line Items: Bike (ID 1234) with quantity 3 and BMW (ID 1236) with quantity 1. On 1-Dec-2020, 1 Bike was received with receipt ID R123, and On 15-Dec-2020, Receipt ID R567 received BMW and other 2 bikes to complete the PO123. So in the final table, because bikes were received in 2 receipts, there are 2 records for Bike for PO123. the PO Line Item no should be the same for bikes

Please help me to work on this column without using any windows functions. Thank you so much

https://stackoverflow.com/questions/65432907/increment-row-numbers-for-groups-mysql December 24, 2020 at 09:26AM