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 assigning 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