How to sort rows in mysql by average?

Question

MySQL

Hello! There is a sign like this:

[--order--]
1
2
3
4
5
6

Tell me, please, is there any trick in mysql to get the data from this table sorted by the average value?
That is, at the beginning of the sample there should be records located closest to the middle.
In this case, I would like to see something like:
3,2,4,1,5,6
Thank you!

Answer 1

There is no muscle at hand, the code is under Oracle, but the idea, I think, is clear:

with inp as (
select 1 id from dual union all
select 2 from dual union all
select 3 from dual union all
select 4 from dual union all
select 5 from dual union all
select 6 from dual)

SELECT
    id
FROM
    inp
ORDER BY
    abs( (
        SELECT
            AVG(id)
        FROM
            inp
    ) - id),
    id;

Answer 2

select 
  table.col,  
  subquery.avg,
  ABS(table.col - subquery.avg) as diff
from table,
  (select AVG(table.col) as avg from table) as subquery
order by
  diff

Answer 3

Add the necessary data to form the sort field (average value) as a left join, and calculate the sort field.
Keep in mind that this will be slow.

select *, (abs(avg_column-column)) as sorting_column from orders i 
left join (select avg(column) as avg_column from orders) o on...
order by sorting_column

Something like this, only you need to think on the joint.