groupnum over partition

Question

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?

You mean, there's no table like `department` where every department name is listed, along with its unique ID? You could use it. — 9000, Jul 14 '11 at 17:23
It's just an example I developed to show the issue. Readlly the group by is done over a bunh of fields on the table, so there is no Id for each group. — Jose L Martinez-Avial, Jul 14 '11 at 20:55

Allan · Accepted Answer · 2011-07-14T21:32:17.333

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

Thanks!! that works perfectly. One question; On your example, how could I group the tables by owner and also by the sum of num_rows, so each group could not have more than 500 rows? — Jose L Martinez-Avial, Jul 14 '11 at 21:00
It's not exactly that. What I wanted on the followup question was to group the tables by owner and by num_rows with a limit of 500. Let's say we have tables A, B, C and D with 100, 200, 250 and 100 rows respectively. Then the first group would include tables A and B(total of 300 rows). Since adding table C would take the row count to more than 500, table C would be included in group 2, same as table D. — Jose L Martinez-Avial, Jul 15 '11 at 05:04
@Jose: That's tricky. I think the windowing aspect of analytic functions might help, but no obvious solution springs to mind. — Allan, Jul 15 '11 at 13:05

groupnum over partition

1 Answers1