MySQL: Sorting duplicate numbers ascendingly

Question

I have a table that looks like this: id value 112131425262. I want to sort them in increasing order, in a circular fashion, in the following way: id value 114221523162. I believe this can be done easily with PHP, but I want to see if it can be done using SQL.

P粉501683874 · Answer

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.