MYSQL: Incremental Sorting of Duplicate Numbers

Question

I have a table that looks like this:

id	values
1	1
2	1
3	1
4	2
5	2
6	2

I would like to have them sorted incrementally in some sort of loop that orders them in this fashion

id	values
1	1
4	2
2	1
5	2
3	1
6	2

I believe this could be done easily with PHP however I would like to see if this can be done using SQL.

Answer 1

Use ROW_NUMBER() window function in the ORDER BY clause:

SELECT *
FROM tablename
ORDER BY ROW_NUMBER() OVER (PARTITION BY `values` ORDER BY id), 
         `values`;

See the demo.