Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to group by or partition by on multiple columns and assign each "group" a number or a variable?

So, I have the following table where

Col1 can have 2 values: A or B

Col2 can have 2 values: P or Q

Col3 can have 3 values: L, M or N

Roll_number is just a numeric Id number

Roll_number , Col1 , Col2, Col3
121212      ,    A ,    P,    L
131313      ,    A ,    P,    L
141414      ,    B ,    P,    M
525252      ,    A ,    Q,    L
626262      ,    B ,    P,    M
929292      ,    A ,    Q,    L
939393      ,    A ,    P,    N
232323      ,    B ,    Q,    L
090909      ,    B ,    P,    L
303030      ,    B ,    Q,    N
505050      ,    A ,    Q,    M
608960      ,    A ,    Q,    L

What I would like to have is the following after "grouping" by on Col1, Col2 and Col3:

Roll_number , Col1 , Col2, Col3, Group_Name
121212      ,    A ,    P,    L,          1
131313      ,    A ,    P,    L,          1

141414      ,    B ,    P,    M,          2
626262      ,    B ,    P,    M,          2

525252      ,    A ,    Q,    L,          3
929292      ,    A ,    Q,    L,          3
608960      ,    A ,    Q,    L,          3

939393      ,    A ,    P,    N,          4
232323      ,    B ,    Q,    L,          5
090909      ,    B ,    P,    L,          6
303030      ,    B ,    Q,    N,          7
505050      ,    A ,    Q,    M,          8

How do I achieve this?

Tags:

2

Answers


  1. 0

    I believe you can use dense_rank () function in MYSQL.

    The ORDER BY clause can be adjusted as per your need. Dense_Rank() is a window function that ranks rows in partitions with no gaps in the ranking values.

    select *, 

dense_rank() over(partition by col1, col2, col3 order by roll_number) as group_name

from table
    Login or Signup to reply.
  2. 0

    It is not quite clear what are the rules for group numbers assignment. One of the ways to give you the exact expected result with the sample data provided could be like this:

    --      S a m p l e    D a t a :
Create Table tbl( roll_number Varchar(6), col_1 varchar(1), col_2 varchar(1), col_3 varchar(1));
Insert Into tbl(roll_number, col_1, col_2, col_3)
VALUES ( '121212', 'A',    'P',    'L' ),
       ( '131313', 'A',    'P',    'L' ),
       ( '141414', 'B',    'P',    'M' ),
       ( '525252', 'A',    'Q',    'L' ),
       ( '626262', 'B',    'P',    'M' ),
       ( '929292', 'A',    'Q',    'L' ),
       ( '939393', 'A',    'P',    'N' ),
       ( '232323', 'B',    'Q',    'L' ),
       ( '090909', 'B',    'P',    'L' ),
       ( '303030', 'B',    'Q',    'N' ),
       ( '505050', 'A',    'Q',    'M' ),
       ( '608960', 'A',    'Q',    'L' );

    … had to use some hardcodings to assigne groups 1, 2 and 3 …

    --      S Q L : 
SELECT   a.roll_number, a.col_1, a.col_2, a.col_3, 
         Case When a.col_1 = 'A' And cnt = 2 Then 1
              When a.col_1 = 'B' And cnt = 2 Then 2
              When a.col_1 = 'A' And cnt = 3 Then 3
         Else Count(Case When cnt = 1 Then 1 End) 
                    Over(Order By rn
                         Rows Between Unbounded Preceding And Current Row) + 3
        End as grp
FROM   ( Select roll_number, col_1, col_2, col_3, 
                Row_Number() Over() as rn, 
                Count(roll_number) Over(Partition By col_1, col_2, col_3) as cnt
        From tbl
       ) a
ORDER BY grp, rn

    /*    R e s u l t : 
roll_number col_1   col_2   col_3   grp
----------- ------- ------- ------ ----
121212      A       P       L         1
131313      A       P       L         1
141414      B       P       M         2
626262      B       P       M         2
525252      A       Q       L         3
929292      A       Q       L         3
608960      A       Q       L         3
939393      A       P       N         4
232323      B       Q       L         5
090909      B       P       L         6
303030      B       Q       N         7
505050      A       Q       M         8    */
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search