MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table storage as
    select 1 as id, 1 as product_id, 15 as storage_id union all
    select 2 as id, 2 as product_id, 17 as storage_id union all
    select 3 as id, 3 as product_id, 18 as storage_id union all
    select 4 as id, 4 as product_id, 17 as storage_id union all
    select 5 as id, 5 as product_id, 15 as storage_id union all
    select 6 as id, 6 as product_id, 17 as storage_id union all
    select 7 as id, 7 as product_id, 18 as storage_id 
select s.*
from storage s
order by row_number() over (partition by storage_id order by id),
         storage_id;


id
product_id
storage_id


1
1
15

2
2
17

3
3
18

5
5
15

4
4
17

7
7
18

6
6
17


select s.*
from (select s.*,
             (@rn := if(@sid = storage_id, @rn + 1,
                        if(@sid := storage_id, 1, 1)
                       )
             ) as seqnum
      from (select s.* from storage s order by storage_id, id) s cross join
           (select @rn := 0, @sid := -1) params
     ) s
order by seqnum, id


id
product_id
storage_id
seqnum


1
1
15
1

2
2
17
1

3
3
18
1

4
4
17
2

5
5
15
2

7
7
18
2

6
6
17
3