Answer
Solution:
You could use row_number to get the desired result:
select company_id as company,
user_id as user_id ,
row_num as row_num
from
( select *,row_number() over(partition by company_id order by id asc) as row_num
from user_company
) as tbl
order by company_id asc,row_num asc
Or if you want the desired result direct from the query try:
select concat('Company: ',company_id,' , User: ',user_id, ', position: ',row_num ) as my_row
from
( select *,row_number() over(partition by company_id order by id asc) as row_num
from user_company
) as tbl
order by company_id asc,row_num asc
Which will give:
my_row
Company: 1 , User: 61, position: 1
Company: 1 , User: 71, position: 2
Company: 1 , User: 81, position: 3
Company: 2 , User: 91, position: 1
Company: 2 , User: 10, position: 2
Company: 2 , User: 11, position: 3
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5a2151db9dc2099f0ae0cdb96c3b7125