1

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.

EFC
  • 1,890
  • 18
  • 39
  • how did you create the helper value? `id % 3 + 1`? You can just do that in the select query can't you ? I'll try if you set up a fiddle for us – erik258 Mar 23 '23 at 19:07
  • What does `SELECT VERSION();` report? Are you using MySQL 8.0 so you can use window functions? – Bill Karwin Mar 23 '23 at 19:11
  • I did not create a real helper column. This is an imaginary construct to help the reader understand what I need, but it does not exist in the actual table. I will edit the question for clarity. – EFC Mar 23 '23 at 19:13
  • Yes, I am using MySQL 8.0+, but I have not heard of window functions. – EFC Mar 23 '23 at 19:14

1 Answers1

1

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;

Dbfiddle

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Wow, thank you for introducing me to a whole new part of MySQL! – EFC Mar 23 '23 at 19:36
  • 2
    It's not just MySQL! They were first introduced in the ANSI SQL:2003 standard. MySQL was rather late to implement them in 2018. – Bill Karwin Mar 23 '23 at 19:39