Nrthugu

I am struggling with this complex query. I am trying to insert the order position of some products.
For example,
I have currently table 1 with a position of NULL, I want to group each Product ID and assign each size a menu position based on ProductID group and using this FIND_IN_SET:

FIND_IN_SET(size,"UNI,XS,S,M,L,XL,XXL,3XL,4XL,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60") asc;

In other words, I want it to look like Table2.

Table1

ID   |   ProductID   |   Size   |   Menu_position

1    |      100      |    S     |   NULL

2    |      100      |    M     |   NULL

3    |      100      |    L     |   NULL

4    |      101      |    40    |   NULL

5    |      101      |    41    |   NULL

6    |      101      |    42    |   NULL

7    |      102      |    XS    |   NULL

8    |      102      |    L     |   NULL

Table2

ID   |   ProductID   |   Size   |   Menu_position

1    |      100      |    S     |   1

2    |      100      |    M     |   2

3    |      100      |    L     |   3

4    |      101      |    40    |   1

5    |      101      |    41    |   2

6    |      101      |    42    |   3

7    |      102      |    XS    |   1

8    |      102      |    L     |   2

What I collected so far:

Number of products Group:select count(distinct ProductID) from Table1

Sort size based on specific order: SELECT * FROM Table1 ORDER BY FIND_IN_SET(size,"UNI,XS,S,M,L,XL,XXL,3XL,4XL,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60") asc;

You can use variables in pre-MySQL 8.0:

SELECT t1.*,

       (@rn := if(@p = productid, @rn + 1,

                  if(@p := productid, 1, 1)

                 )

       ) as menu_position

FROM (SELECT t1.*

      FROM Table1 t1

      ORDER BY ProductId,

               FIND_IN_SET(size, 'UNI,XS,S,M,L,XL,XXL,3XL,4XL,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60') asc

     ) AS alias CROSS JOIN

     (SELECT @p := -1, @rn := 0) params;

In MySQL 8+, this is much simpler:

select t1.*,

       row_number() over (partition by productid order by FIND_IN_SET(size, 'UNI,XS,S,M,L,XL,XXL,3XL,4XL,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60')) as menu_position

from table1 t1

Create a two-column table containing all the the Size values in one column and the integer order of those sizes in the second column--call that table menu_pos. Join this to your Table on size, to produce a table or view (call this product_pos) containing columns product_id, size, and menu_pos. Then modify the menu_pos values to ensure that they are strictly sequential using a window function, such as:

select

    product_id,

    size,

    rank() over (partition by product_id order by menu_pos) as new_menu_pos

from

    product_pos;

Window functions require MySQL 8.