sort by MySQL special group

Question

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

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

Table A

Table B shows results that I want to get. As you can see the groups are in round-robin order.

Table B

score 3 · Accepted Answer · answered Aug 09 '15 at 12:41

3

If I understand the question, you want to sort the output so the groups are in a round-robin fashion rather than ordered. You can do this by enumerating the values within each group and then using that information for sorting:

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;

answered Aug 09 '15 at 12:41

Gordon Linoff

1,242,037
58
646
786

1

exactly as I want, thank you. Sorry for bad english. – sonik509 Aug 09 '15 at 13:51
hi, The full-text score less than 7 at the beginning of the sequence of ones I don't want to be. How Can I do this ? – sonik509 Aug 23 '15 at 16:39
@user3236432 . . . I'm not sure I fully understand. You can put a `>` condition on the match, if you are looking for a threshold for that. Perhaps you should ask another question. – Gordon Linoff Aug 23 '15 at 17:00

score 1 · Answer 2 · answered Aug 09 '15 at 13:26

Consider a subquery in a derived table to calculate a group number to be sorted at 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

sort by MySQL special group

2 Answers2