Search code examples
sqlmysqlsql-order-bywindow-functionssql-rank

MYSQL: Incremental Sorting of Duplicate Numbers

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.

Solution

  • 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.