MySQL - Order by category_id try to avoid having duplicates close to each other

Question

Please bear with my English.

I have a table like this,

 id |  category_id  |  product_id
 -----------------------------------
  1 |  1            |  1
  2 |  1            |  2
  3 |  2            |  1
  4 |  2            |  3
  5 |  1            |  4
  6 |  3            |  5

I want the output to be,

id  |  category_id  |  product_id
----------------------------------
1   |  1            |  1
3   |  2            |  1
6   |  3            |  5
2   |  1            |  2
4   |  2            |  3
5   |  1            |  4

So in short what I need is, the category_id must be ordered so that it repeats in cycles like 1, 2, 3, 1, 2, 3,...etc.

Answer 1

Here is a query which gives you that result :

SELECT p1.*
FROM `product` p1
JOIN `product` p2
      ON p2.category_id = p1.category_id 
      AND p2.id <= p1.id
GROUP BY p1.id
ORDER BY COUNT(*),category_id;

Where product is your table.

See DEMO HERE

Answer 2

...try to avoid having duplicates close to each other

Well, okay. The simplest solution I can come up with is a GROUP BY 'product_id' ORDER BY 'category_id'. This would make your table look like this:

id  |  category_id  |  product_id
----------------------------------
1   |  1            |  1
3   |  2            |  1
2   |  1            |  2
4   |  2            |  3
5   |  1            |  4
6   |  3            |  5

I assume that "category_id must be ordered so that it repeats in cycles" is a translation of "I don't want duplicates near each other".

If you had three categories on product one and on product two, they would group together (1,1,1,2,2,2) but the categories would be numerically ordered (1,2,3,1,2,3). Its not a perfect solution for what you want but it is a simple one that seems you give you what you need.

Any complicated hackery in your query will only slow down the query. While it may not be a problem now, it could be a big problem if you have a lot of records.