mysql select counter of current position

Question

Having a table like this

+--------+------------+
| orderID | productID |
+--------+------------+
|    100 |         10 |
|    100 |         11 |
|    101 |         10 |
|    101 |         11 |
|    101 |         12 |
+--------+------------+

I need to add a third column, which be counter of current position of product. Like:

+--------+------------+--------------+
| orderID | productID | currentIndex | 
+--------+------------+--------------+
|    100 |         10 |      1       |
|    100 |         11 |      2       |
|    101 |         10 |      1       |
|    101 |         11 |      2       |
|    101 |         12 |      3       |
+--------+------------+--------------+

Can help me, please?

I have now this query:

SELECT orderID, productID
FROM orders;

Answer 1

If you are running MySQL 8.0, `row_number() does exactly what you ask for:

select orderid, productid, 
    row_number() over(partition by orderid order by productid) currentindex
from orders;

In earlier versions, alternatives are either user variables, or a correlated subquery. I am going to recommend the second option - user variables are rather tricky to work with, and are now officially planned for future deprecation:

select orderid, productid, 
    (select count(*) from orders o1 where o1.orderid = o.orderid and o1.productid <= o.productid) currentindex
from orders o;