CODEWITHSUNDEEP
mysqlsqlif-statementwhile-loop
Beck
Beck

Reputation: 27

How can you update a table with an ascending order position based on different groups in MYSQL?

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;

Upvotes: 1

Views: 337

Answers (2)

rd_nielsen
rd_nielsen

Reputation: 2459

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270311

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

Upvotes: 1

Related Questions