title: SQL: enumerate returned rows within each group
body: <p>Suppose I have a <code>SELECT ...</code> query that returns sth. like this:</p>
  
  <pre><code>role      name
  --------  -------
  MANAGER   Alice
  WORKER    Bob
  WORKER    Evan
  WORKER    John
  MANAGER   Max
  WORKER    Steve
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Usage of Oracle extensions is fine.</p>