MySql outputs conditionally ascending

advertisements

Trying to sort rows from lowest to highest continually, or rather repeatedly using MySql. For example: if a column has the following values: 1,3,2,4,2,1,4,3,5, then it should end up like this 1,2,3,4,5,1,2,3,4. So it goes from lowest to highest, but tries to sort again from lowest to highest multiple times.


For large sets, the semi-JOIN operation (the approach in the answer from Strawberry) may create an unwieldy resultset. (Then again, MySQL may have some optimizations in there.)

Another alternative available in MySQL is to use "user variables", like this:

SELECT r.mycol
  FROM ( SELECT IF([email protected],@seq := @seq + 1,@seq := 1) AS seq
              , @prev := q.mycol AS mycol
           FROM mytable q
           JOIN (SELECT @prev := NULL, @seq := NULL) p
          ORDER BY q.mycol
       ) r
 ORDER BY r.seq, r.mycol

Let me unpack that a bit, and explain what it's doing, starting with the inner query (inline view aliased as r.) We're telling MySQL to get the column (mycol) containing the values you want to sort, e.g. 1,3,2,4,2,1,4,3,5 and we're telling MySQL to order these in ascending sequence: 1,1,2,2,3,3,4,4,5.

The "trick" now is to use a MySQL user variable, so that we can compare the mycol value from the current row to the mycol value from the previous row, and we use that to assign an ascending sequence value, from 1..n on each distinct value.

With that resultset, we can tell MySQL to order by that assigned sequence value first, and then by the value from mycol.


If there is a unique id on each row, then a correlated subquery can be used to get an equivalent result (although this approach is very unlikely to perform as well on large sets)

 SELECT r.mycol
   FROM mytable r
  ORDER
     BY ( SELECT COUNT(1)
            FROM mytable q
           WHERE q.mycol = r.mycol
             AND q.id <= r.id
        )
      , r.mycol

Here's the setup for the test case:

CREATE TABLE mytable (id INT, mycol INT);
INSERT INTO mytable (id, mycol) VALUES
(1,1),(2,3),(3,2),(4,4),(5,2),(6,1),(7,4),(8,3),(9,5);