Welcome to ORDER BY jungle

SQL is fun and not at all boring. The latest article by Markus Winand on Order by Has Come a Long Way sent me on quite a journey. First, set up a table called nums with one integer column and four rows: CREATE TABLE nums (a int); INSERT INTO nums VALUES (0), (1), (2), (3); Try to guess what these two queries return. SELECT -a AS a FROM nums ORDER BY a; SELECT -a AS a FROM nums ORDER BY -a; Most of us would guess the same rows in a different order. The actual answer is that they produce exactly the same rows in exactly the same order. By the same logic you might expect SELECT a AS c FROM nums ORDER BY -c; to do exactly the same. Except it does not. It errors with column "c" does not exist despite the alias being right there in the statement. Welcome to ORDER BY jungle. Names and expressions are not the same If you ask most developers how ORDER BY works, they will say "you put a column name there and it sorts the rows". In 99% of queries that is exactly what happens. People sort by…