CODEWITHSUNDEEP
sqlsql-servert-sql
HRISHIKESH BAGCHI
HRISHIKESH BAGCHI

Reputation: 33

Group specific number of rows together and generate a group id

I have a table A with 2 columns - Roll and Subject. It contains these rows:

Roll Subject
 1   Robots
 2   Robots
 3   Robots
 4   Robots
 5   Robots
 6   Space
 7   Space
 8   Space
 9   Space
10   Neurons
11   Neurons

I need to write a query and produce output as follows:

Roll Subject GroupId
   1 Robots  1
   2 Robots  1
   3 Robots  1
   4 Robots  2
   5 Robots  2
   6 Space   3
   7 Space   3
   8 Space   3
   9 Space   4
  10 Neurons 5
  11 Neurons 5

Upvotes: 0

Views: 79

Answers (3)

EzLo
EzLo

Reputation: 14209

Try the following.

The -1 before the ROW_NUMBER is used so that the ROW_NUMBER values 1, 2 and 3 (which will be 0, 1 and 2) are grouped together when divided by 3, since the result is 0 (INT division).

The minimum roll by subject is to split the DENSE_RANK so it gets a new GroupID number when the subject changes.

;WITH SampleData AS
(
    SELECT
        A.Roll,
        A.Subject,
        RowNumberBySubject = -1 + ROW_NUMBER() OVER (PARTITION BY A.Subject ORDER BY A.Roll ASC),
        MinRollBySubject = MIN(A.Roll) OVER (PARTITION BY A.Subject)
    FROM
        (VALUES
            (1,'Robots'),
            (2,'Robots'),
            (3,'Robots'),
            (4,'Robots'),
            (5,'Robots'),
            (6,'Space'),
            (7,'Space'),
            (8,'Space'),
            (9,'Space'),
            (10,'Neurons'),
            (11,'Neurons') 
            ) A(Roll, [Subject])
)
SELECT
    S.Roll,
    S.Subject,
    S.RowNumberBySubject,
    S.MinRollBySubject,
    GroupIDBySubject = S.RowNumberBySubject / 3,
    GroupId = DENSE_RANK() OVER (
        ORDER BY 
            S.MinRollBySubject, 
            S.RowNumberBySubject / 3) -- GroupIDBySubject
FROM
    SampleData AS S
ORDER BY
    S.Roll ASC

Result:

Roll    Subject RowNumberBySubject  MinRollBySubject    GroupIDBySubject    GroupId
1       Robots  0                   1                   0                   1
2       Robots  1                   1                   0                   1
3       Robots  2                   1                   0                   1
4       Robots  3                   1                   1                   2
5       Robots  4                   1                   1                   2
6       Space   0                   6                   0                   3
7       Space   1                   6                   0                   3
8       Space   2                   6                   0                   3
9       Space   3                   6                   1                   4
10      Neurons 0                   10                  0                   5
11      Neurons 1                   10                  0                   5

Upvotes: 1

Ilyes
Ilyes

Reputation: 14926

No just simple grouping but grouping max limit is 3

I don't understand the logic here, but according to your comment I think you are looking for

SELECT *, 
      ((ROW_NUMBER() OVER(ORDER BY (SELECT 1)) - 1) / 3) + 1 GroupId
FROM T

Returns:

+------+---------+---------+
| Roll | Subject | GroupId |
+------+---------+---------+
|    1 | Robots  |       1 |
|    2 | Robots  |       1 |
|    3 | Robots  |       1 |
|    4 | Robots  |       2 |
|    5 | Robots  |       2 |
|    6 | Space   |       2 |
|    7 | Space   |       3 |
|    8 | Space   |       3 |
|    9 | Space   |       3 |
|   10 | Neurons |       4 |
|   11 | Neurons |       4 |
+------+---------+---------+

Demo

Upvotes: 1

JustFanOfYou
JustFanOfYou

Reputation: 21

This is what you expect

SELECT *,NTILE(3) OVER(ORDER BY [Roll])AS GroupID FROM 
(
VALUES
(1,'Robots'),
(2,'Robots'),
(3,'Robots'),
(4,'Robots'),
(5,'Robots'),
(6,'Space'),
(7,'Space'),
(8,'Space'),
(9,'Space'),
(10,'Neurons'),
(11,'Neurons') 
)A (Roll,[Subject])

Upvotes: 0

Related Questions