Mysql - How do I order results by alternating (1,2,3, 1, 2, 3, 1, 2, 3,) rows, is it possible?

Question

I want to order my results by client 1, 2, 3, then again client 1, 2, 3, and so on.

Is there a way to do this without using a for loop or making three separate queries? Not only that, but I am working with paginated data so it needs to return x results, but consistently.

Any ideas? GROUP BY maybe?

client_id  project_id  project_name  
---------- ----------  ------------
 1         42          project abc
 2         49          project xyz
 3         41          project 123
 1         22          project apple
 2         29          project orange
 3         21          project banana

Answer 1

Use:

SELECT x.client_id, 
       x.project_id,
       x.project_name
  FROM (SELECT t.client_id,
               t.project_id,
               t.project_name,
               CASE
                 WHEN @client_id != t.client_id THEN @rownum := 0
                 WHEN @client_id = t.client_id THEN @rownum := @rownum + 1
                 ELSE @rownum 
               END AS rank,
               @client_id := t.client_id
          FROM TABLE t,
               (SELECT @rownum := 0, @client_id
      ORDER BY t.client_id) r) x
ORDER BY x.rank, x.client_id

MySQL doesn't have any ranking functionality, but luckily you can use variables. The key was resetting the @rownum value when the client_id doesn't match the previous client_id - the ORDER BY in the subquery is to ensure that clients are in order.