CODEWITHSUNDEEP
sqloracle-database
user319799
user319799

Reputation:

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.

Upvotes: 5

Views: 4657

Answers (1)

user330315
user330315

Reputation:

Use a window function:

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

Upvotes: 5

Related Questions