Sort by MySQL special group

Question

Table A shows the results that I performed by running the following SQL in MySQL.

SELECT * FROM table WHERE MATCH (title) AGAINST ('marka tv') 

Table a

Table B shows the results that I want to get. As you can see, the groups are in a circular order.

Table B

Answer 1

If I understand the question, do you want to sort the output so that the groups are rounded rather than ordered. You can do this by listing the values ​​within each group and then using this information to sort:

 SELECT t.* FROM (SELECT t.*, (@rn := if(@g = groups, @rn + 1, if(@g := groups, 1, 1) ) ) as rn FROM table t CROSS JOIN (SELECT @rn := 0, @g := '') params WHERE MATCH (title) AGAINST ('marka tv') ORDER BY groups ) t ORDER BY rn, groups; 

Answer 2

Consider the subquery in the view to calculate the group number for sorting in the final table:

 SELECT f.* FROM (SELECT t1.* , (SELECT count(*) FROM table t2 WHERE (t2.title <= t1.title) AND (t1.groups = t2.groups)) AS groupNo FROM table t1 WHERE MATCH (t1.title) AGAINST ('marka tv') ) AS f ORDER BY groupNo, groups