SQL Server 2022

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

CREATE TABLE test (id int, f1 varchar(20));
INSERT INTO test VALUES (1, 'foo');
INSERT INTO test VALUES (2, 'bar');

2 rows affected

SELECT id, (SELECT count(*) FROM test) as total_count
FROM test
ORDER BY total_count


id
total_count


1
2

2
2


SELECT id, (SELECT count(*) FROM test) as total_count
FROM test
ORDER BY CASE WHEN 1=1 THEN total_count END
Msg 207 Level 16 State 1 Line 3
Invalid column name 'total_count'.