CODEWITHSUNDEEP
sql
smilence
smilence

Reputation: 379

Why `ORDER BY` can use nonexist column at its execution time?

Say I have a movies table and an actors table with movie_id foreign key

SELECT movies.id, movies.title
JOIN actors
ON movies.id = actors.movie_id
GROUP BY movies.id
ORDER BY COUNT(movies.id) DESC

This will correctly give me the movies ordered by actor counts.

But I thought the first 4 lines, should generate a table like

id title 
2. Titanic
3. Fight club
1. Casablanca

Each movie id should only appear once, how could ORDER BY order by the count of it? Unless SQL is doing some underneath magic to secretly calculate COUNT(movies.id) when executing the first 4 lines

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Interesting question. ORDER BY can use any expression that could be in the SELECT. Internally, SQL does the calculation and so it can be used.

This not only applies to functions such as COUNT(*), but also functions with arguments. Or any expression of them. For instance, this would be allowed:

ORDER BY LENGTH(movies.title) DESC

to get the longest movie names first -- well assuming your database has a LENGTH() function.

Upvotes: 2

Related Questions