groupnum over partition

I've got a Oracle 11g table as follows:

id  name    department
1   John    Accounting
2   Frederick   BackOffice
3   Erick   Accounting
4   Marc    BackOffice
5   William BackOffice
6   Pincton Accounting
7   Frank   Treasury
8   Vincent BackOffice
9   Jody    Security

I want to query the table getting all records, and for each record as开发者_C百科signing a sequence to the department, so the result would be something like this:

1   John    Accounting  1
2   Frederick   BackOffice  2
3   Erick   Accounting  1
4   Marc    BackOffice  2
5   William BackOffice  2
6   Pincton Accounting  1
7   Frank   Treasury    4
8   Vincent BackOffice  2
9   Jody    Security    3

I know I can get the sequence number for each record inside their department, using rownum over partition by..., the question is how I can do the 'same' at a group level, assigning a sequence to each group(let's say the order is by department name, as in the example). Any ideas?

Using ALL_TABLES as a demonstration ("owner" instead of "department", "table_name" instead of "name"), I think dense_rank will give you what you want:

SELECT owner, 
       table_name, 
       ROW_NUMBER () OVER (PARTITION BY owner ORDER BY table_name) as table_seq, 
       DENSE_RANK () OVER (ORDER BY owner) as owner_seq
FROM   all_tables

The reason this works is that dense_rank provides the ranking over the order provided. Whichever owner (department) comes first is a tie across all instances of that owner, so all of those records have a rank of 1. Since we're using dense_rank instead of rank, all those ties don't count for incrementing the rank, so the next owner gets the rank of 2.

If I understand your followup question correctly, using my example, you want to display every owner and the first 500 tables for each owner? In that case, you really just want to filter based on table_seq, so you have to use a sub-query:

SELECT * 
FROM   (SELECT owner, 
               table_name, 
               ROW_NUMBER () OVER (PARTITION BY owner ORDER BY table_name) as table_seq, 
               DENSE_RANK () OVER (ORDER BY owner) as owner_seq
        FROM   all_tables)
WHERE  table_seq <= 500