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

Why not ORDER BY id?

Answer 2

GROUP BY is not going to be helpful. Whether this is possible is going to depend a lot on your data. Basically you would need a convoluted ORDER BY that hacked something together based on other values.

For instance, using the example data you gave, you could use:

ORDER BY FLOOR(project_id / 10), client_id

This is unlikely to be useful on your real data but it gives the idea that you would need to take a separate field, make some subsets of that data be equivalent for the purpose of sorting (in this case, everything with the same 10s value) and the have a secondary sort on client_id.

Although it depends a lot on the final formula, something like this should be a relatively stable sort, so adding pagination, such as via

LIMIT 10, 10

should return consistent results.

Of course, doing this means you get no benefits from having indexes so if you have a lot of rows, it will end up being slower than doing something with loops/separate queries.

Answer 3

Unless I read the question wrong is this what you want?

SELECT *
FROM table
WHERE client_id in (1, 2, 3)
ORDER by id, client_id

Answer 4

Sounds like something to be done client-side.

Answer 5

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.