Return a result set in numeric pattern / sequence

Question

I have a problem in hand and that is to fetch a result set in a numeric pattern / sequence based on a single column's value.

Say I have the following table:

+-------+  
| val   |  
+-------+
| 1     |
| 1     |
| 1     |
| 1     |
| 2     |
| 2     |
| 2     |
| 2     |
| 3     |
| 3     |
| 3     |
| 3     |
+-------+

How can I make it this way instead:

+-------+  
| val   |  
+-------+
| 1     |
| 2     |
| 3     |
| 1     |
| 2     |
| 3     |
| 1     |
| 2     |
| 3     |
| 1     |
| 2     |
| 3     |
+-------+

Case mentioned by aularon:

What if one of the values do not have enough to fill the gaps, what is the expected behaviour? Say we replace two of the 3's to be 8's

+-------+  
| val   |  
+-------+
| 1     |
| 1     |
| 1     |
| 1     |
| 2     |
| 2     |
| 2     |
| 2     |
| 3     |
| 3     |
| 8     |
| 8     |
+-------+

I'd still expect it to go in numeric sequence of smallest first, then largest, then smallest, then largest.

+-------+  
| val   |  
+-------+
| 1     |
| 2     |
| 3     |
| 8     |
| 1     |
| 2     |
| 3     |
| 8     |
| 1     |
| 2     |
| 1     |
| 2     |
+-------+

Answer 1

This is what we call a major rownum hack. Let's say your table is named t and your value column is named val. This will give the result you're looking for.

SELECT val    /* step 6 */
FROM
(SELECT A.val, 
   CASE 
   WHEN A.val <> IFNULL(B.val,-1) THEN @q:=0   /* step 4 */
   ELSE @q:=@q+1                               /* step 4 */ 
    END AS seq                                 /* step 4 */
   FROM
    (SELECT @q:=0)r,
    (SELECT                          /* step 1 */
            @a:=@a+1 AS n,           /* step 1 */
            val                      /* step 1 */
      FROM  (SELECT @a:= 0)r, T      /* step 1 */
     ORDER BY val) A                 /* step 1 */
   LEFT JOIN
    (SELECT                        /* step 2, like step 1 */
            @b:=@b+1 AS n, 
            val
       FROM  (SELECT @b:= 0)r, T
     ORDER BY val) B
    ON B.n = A.n-1    /* step 3 */
 ORDER BY seq,val) s  /* step 5 */

A word or two of explanation:

1. We need to turn your val table into a val,n table (where n is the row number)

2. Then we need two of those, with independent row numbers @a and @b

3. Then we need to join them together with a row = row-1 offset to compare adjacent val columns.

4. Then we need to apply a sequence number to each val column, that resets. That is, we need to give sequence numbers 1,2,3 to the 1s, then the 2s, etc.

5. Then we need to sort by seq, then val.

6. Then we need to conceal the seqs.

Lo and behold....

Hack hack!