Mysql repeating order

Question

Lets say I have the following table:

| name   | category  |
----------------------
| Apple  | fruit     |
| Banana | fruit     |
| Orange | fruit     |
| Pear   | fruit     |
| Tomato | vegetable |
| Pepper | vegetable |
| Walnut | nut       |
| Cashew | nut       |

How do I get results to order in bulks with only one row from category, but then repeat with the rest like so: fruit, vegetable, nut, fruit, vegetable, nut, fruit, vegetable... and so on. I want to achieve this result:

| name   | category  |
---------------------
| Apple  | fruit     | ─┐
| Tomato | vegetable | ─├ Bulk 1
| Walnut | nut       | ─┘ 
| Banana | fruit     | ─┐
| Pepper | vegetable | ─├ Bulk 2
| Cashew | nut       | ─┘ 
| Orange | fruit     | ── Bulk 3
| Pear   | fruit     | ── Bulk 4

Each time my query will be different but I would like to have the same ordering for the results. Bulks don't have to contain all of the categories (ex. Bulk 3 & 4 contain only one row).

Can this be achieved with a single query/PDO transaction?

You rank them, then order by the modulus – Strawberry Jul 09 '16 at 18:19 — Strawberry, Jul 09 '16 at 18:19

score 1 · Accepted Answer · edited May 23 '17 at 11:54

Here is your query for mysql:

SELECT  name,category FROM (
    SELECT    id,
                  name,
                  category,
                 ( 
                    CASE category 
                    WHEN @curCategory 
                    THEN @curRow := @curRow + 1 
                    ELSE @curRow := 1 AND @curCategory := category END
                  ) + 1 AS rank
        FROM      plants p,
                  (SELECT @curRow := 0, @curCategory := '') r
       ORDER BY rank,category
 ) as pl

To write it I use this. The outer select is only for choose two columns name and category. The main idea is inside inner select which create 'rank' column witch numerate separatley each category. And then we sort by rank (and inside rank we sort by category). Run only inner select without (and with) order by then you will see what i mean.

Mysql repeating order

1 Answers1