MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT VERSION();


VERSION()


8.0.26


CREATE TABLE test (id INT, val INT);
INSERT INTO test VALUES
(1,1),
(1,2),
(2,3),
(2,4);
SELECT * FROM test;


id
val


1
1

1
2

2
3

2
4


-- Incorrect ordering
SELECT id, AVG(val) AS average, 1 - AVG(val) ordering_expression
FROM test
GROUP BY id
ORDER BY 1 - average;


id
average
ordering_expression


1
1.5000
-0.5000

2
3.5000
-2.5000


-- How to fix
SELECT id, AVG(val) AS average, 1 - AVG(val) ordering_expression
FROM test
GROUP BY id
ORDER BY 1 - AVG(val);


id
average
ordering_expression


2
3.5000
-2.5000

1
1.5000
-0.5000