SQL: enumerate returned rows within each group

Question

Suppose I have a SELECT ... query that returns sth. like this:

role      name
--------  -------
MANAGER   Alice
WORKER    Bob
WORKER    Evan
WORKER    John
MANAGER   Max
WORKER    Steve

Is it possible to add another column that enumerates rows within each group (i.e. managers, workers and people on any other role are enumerated with no regard to other roles)? Like this:

role      name     no.
--------  -------  ----
MANAGER   Alice     1
WORKER    Bob       1        // second row, but the first worker
WORKER    Evan      2
WORKER    John      3
MANAGER   Max       2        // fifth row, but only the second manager
WORKER    Steve     4

Usage of Oracle extensions is fine.

Answer 1

Use a window function:

select role, name, 
       row_number() over (partition by role order by name) as rn
from the_table
order by name;