Grouping using analytical functions in Oracle

Question

I've got a table with the columns:

• col1 (primary key) number
• col2 number - contains few duplicates

This table contains 600 records. I'm trying to construct a query where the user can give any group count (max no of records in a group. Eg: 11). The result will be the total records with a group number. Here same data in col2 should not be in two different groups.

Example — consider this query:

select col1, col2, round(rownum-1 /: group_size) as group_number from group_tester;

This query will give the group number, but I find few records with same data falling into different groups.

Is there any way to solve my problem with analytical functions?

---

here is a data sample and expected result

Data Sample

col1(student id)                          col2(student Rank)
1                                                3
2                                                3
3                                                7
4                                                3
5                                                2
6                                                1
7                                                5
8                                                5
9                                                4
10                                               6
11                                               9
12                                               8

if the group size is 4, i want the results to be as below

Expected Result

col1(student id)    col2(student Rank)          group number
1                          3                          2
2                          3                          2
3                          7                          2
4                          3                          2
5                          2                          1
6                          1                          1
7                          5                          3
8                          5                          3
9                          4                          1
10                         6                          1
11                         9                          3
12                         8                          3

my task is to break the number of students into groups of size four, but two groups should not have same ranked student

Answer 1

If you just want a unique number for each row within groupings of col2, try using row_number() over() as follows:

row_number() over(partition by col2 order by col1) as group_number 

Consider "partition by" as similar to "group by", but each row is returned instead of being summarized.