Set row number for batch of N rows per category

I have table

+-----+----------+---------+
| id  | name     | phase   |
+-----+----------+---------+
| 101 | Bolt     | PHASE 1 |
| 102 | Nut      | PHASE 1 |
| 103 | Screw    | PHASE 1 |
| 104 | Hex BOLT | PHASE 1 |
| 105 | Rubber   | PHASE 1 |
| 106 | Aluminum | PHASE 2 |
| 107 | Slate    | PHASE 2 |
| 108 | Pen      | PHASE 3 |
| 109 | Pencil   | PHASE 3 |
| 110 | Mouse    | PHASE 3 |
| 111 | Keyboard | PHASE 3 |
+-----+----------+---------+

I want to create a another column in which i have to put row number.

Logic : For 3 rows, it should be same and change to next value for 4th row. And Whenever the PHASE is changing, it should go to next number even if the set of 3 is not complete for the previous number.

Expected Output

+-----+----------+---------+-----+
| id  | name     | phase   | SET |
+-----+----------+---------+-----+
| 101 | Bolt     | PHASE 1 | 1   |
| 102 | Nut      | PHASE 1 | 1   |
| 103 | Screw    | PHASE 1 | 1   |
| 104 | Hex BOLT | PHASE 1 | 2   |
| 105 | Rubber   | PHASE 1 | 2   |
| 106 | Aluminum | PHASE 2 | 3   |
| 107 | Slate    | PHASE 2 | 3   |
| 108 | Pen      | PHASE 3 | 4   |
| 109 | Pencil   | PHASE 3 | 4   |
| 110 | Mouse    | PHASE 3 | 4   |
| 111 | Keyboard | PHASE 3 | 5   |
+-----+----------+---------+-----+

I have tried the below query, but it is not giving me the required output.

select *, (row_number() over (order by phase)-1) / 3 as sets
from table_main

Actual Output:

+-----+----------+---------+------+
| id  | name     | phase   | sets |
+-----+----------+---------+------+
| 101 | Bolt     | PHASE 1 | 0    |
| 102 | Nut      | PHASE 1 | 0    |
| 103 | Screw    | PHASE 1 | 0    |
| 104 | Hex BOLT | PHASE 1 | 1    |
| 105 | Rubber   | PHASE 1 | 1    |
| 106 | Aluminum | PHASE 2 | 1    |
| 107 | Slate    | PHASE 2 | 2    |
| 108 | Pen      | PHASE 3 | 2    |
| 109 | Pencil   | PHASE 3 | 2    |
| 110 | Mouse    | PHASE 3 | 3    |
| 111 | Keyboard | PHASE 3 | 3    |
+-----+----------+---------+------+

I have tried with DENSE_RANK() as well but not getting the expected output.

FIDDLE HERE

Answer

Give it a try:

WITH CTE
as
(
select *, ROW_NUMBER () OVER (PARTITION BY phase ORDER BY id) as rn
from table_main
)
SELECT *,DENSE_RANK() over (ORDER BY phase ,CEILING(rn/3.0)) as set
FROM CTE

Advertisement

Answer