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
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);