I need to use a single query to sort a table into an order where there are three even batches of sorted records.
Here is an example table…
id value
1 356
2 243
3 321
4 123
5 654
6 222
7 102
8 900
9 489
A simple ORDER BY value DESC would produce
id value
8 900
5 654
9 489
1 356
3 321
2 243
6 222
4 123
7 102
However, what I need is this list ordered in three equal parts, like this (the blank lines are added for clarity)…
id value
8 900
1 356
6 222
5 654
3 321
4 123
9 489
2 243
7 102
If I could add an imaginary temporary helper column to the initial sort, such as this (note, this helper column does not really exist in my table, I am only adding it here to help illustrate the problem)…
id value helper
8 900 1
5 654 2
9 489 3
1 356 1
3 321 2
2 243 3
6 222 1
4 123 2
7 102 3
Then I could use ORDER BY helper, value to get the sort I want…
id value helper
8 900 1
1 356 1
6 222 1
5 654 2
3 321 2
4 123 2
9 489 3
2 243 3
7 102 3
But, again, the helper column is imaginary and I don’t really have it in my table. I can’t see how to get this result from a single query given the data I do have in the table. I would appreciate any hints you might have.
It would be best if the solution could be generalized so that any number of batches could be created. These don’t have to be exactly the same size, they might differ by one in length in cases where the total number of records does not evenly divide into the batch size.
>Solution :
Here’s an example of a solution using window functions.
SELECT id, value, (rownum+2) % 3 AS helper
FROM (
SELECT id, value,
ROW_NUMBER() OVER (ORDER BY value DESC) AS rownum
FROM mytable
) AS t
ORDER BY helper, value DESC;
Window functions are an interesting and powerful new feature in MySQL 8.0. I recommend reading the whole section of the manual here: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html