doublep doublep - 5 months ago 37
SQL Question

SQL: enumerate returned rows within each group

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

Use a window function:

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