How to order rows in a repeating sequence?

Question

I have a table like this:

+----+--------+--------------------+
|  1 | Apple  | Message 1          |
|  2 | Orange | Message two        |
|  3 | Lime   | Some data          |
|  4 | Banana | More data          |
|  5 | Lime   | Boom. This is data |
|  6 | Pear   | I'm on 6 row       |
|  7 | Orange | Foo and Bar here   |
|  8 | Banana | Remember Baz       |
|  9 | Apple  | Big fat juicy data |
| 10 | Lime   | More values here   |
| 11 | Pear   | I'm almost empty   |
+-------------+--------------------+

And I have some sequence, like:

(Banana, Apple, Orange, Lime, Pear)

So I need to sort it like this:

+----+--------+--------------------+
|  4 | Banana | More data          |
|  1 | Apple  | Message 1          |
|  2 | Orange | Message two        |
|  3 | Lime   | Some data          |
|  6 | Pear   | I'm on 6 row       |
|  8 | Banana | Remember Baz       |
|  9 | Apple  | Big fat juicy data |
|  7 | Orange | Foo and Bar here   |
|  5 | Lime   | Boom. This is data |
| 11 | Pear   | I'm almost empty   |
| 10 | Lime   | More values here   |
+-------------+--------------------+

How can I sort it like that and also have a pagination?

---

Or, generally, I need to sort rows so every N rows in the results set will be unique.

How to?

Answer 1

Try this one:

First I use variable to see what is the first aparition of each fruit. That way I can show the first apparition of each fruit together (rn = 1)

Then use a CASE in the ORDER BY to sort following your sequence.

The problem I see is LIME = 3 is alone.

SQL DEMO

SELECT `ID`, `Fruit`, `Msg`, rn
FROM (
    SELECT *,
           @rn := IF(@fruit = `Fruit`,
                     @rn + 1,
                     if(@fruit := `Fruit`, 1, 1) 
                    ) as rn
    FROM Table1
    CROSS JOIN (SELECT @rn := 0, @fruit := '') as var
    ORDER BY `Fruit`, `ID`
    ) T
ORDER BY rn, CASE `Fruit`  
                  WHEN 'Banana' THEN 1
                  WHEN 'Apple'  THEN 2
                  WHEN 'Orange' THEN 3
                  WHEN 'Lime'   THEN 4
                  WHEN 'Pear'   THEN 5
             END

OUTPUT

Tips:

• CROSS JOIN (SELECT @rn := 0, @fruit := '') as var Here you create and initialize the variables @rn and @fruit.
• ORDER BY Fruit, ID Now I order by fruits so the same fruit are together. also order by ID so the smaller id appear first.
• @rn := now you calculate the rn for each row.
• IF(@fruit = Fruit, @rn + 1, if(@fruit := Fruit, 1, 1) )
  • You need to increase the @rn if you have the same fruit. like first apple, second apple,etc
  • if you get a new fruit @fruit will chage because I use := and reset @rn counter to 1.
  • The first time you compare @fruit = Fruit you go for the second part of the IF because is initialized as ''