Как отсортировать строки в mysql по среднему значению?

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;

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

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