MySQL: Sorting duplicate numbers ascendingly

Question

I have a table that looks like this:

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

I want to sort them ascendingly this way in some kind of loop.

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

I believe this can be done easily with PHP, but I wanted to see if I could do it with SQL.

Answer 1

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

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

You can take a look at this demo.