MySQL Add Numbers

I have db with company id numbers and employers, now i need to know who is first, second ... employer entered in db. For this I need to add number for each one like this:

CompanyID   NameOfEmp       PositionInDB
111         Emp1            1
111         Emp2            2
111         Emp3            3
112         Emp1            1
112         Emp2            2
113         Em开发者_运维问答p1            1
114         Emp1            1
114         Emp2            2 

Tables are stored in an undefined order. In order to define a position, you have to define an order yourself. This example assigns an order based on the name of the employee. It will number employees alfabetically per company:

select  *
,       (
        select  count(*) + 1
        from    YourTable yt2
        where   yt2.CompanyID = yt1.CompanyID
                and yt2.NameOfEmp < yt1.NameOfEmp
        ) as PositionInDb
from    YourTable yt1

Since you're using MySQL, you could also use a variable:

set @num  = 1;

select  CompanyID
,       NameOfEmp
,       @num := if(@last_comp = CompanyID, @num + 1, 1) as PositionInDb
,       @last_comp := CompanyID as dummy
from    YourTable
order by
        CompanyID
,       NameOfEmp

Variables are fast and easy, but not portable, and most DBA's dislike them.