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.
Advertisement
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