Do column references have to follow the order the columns are listed in the SELECT?

Question

Do column references have to follow the order the columns are listed in the SELECT?

Answer

No, once you list the columns after the SELECT, they can be referenced by the order they appeared, starting from 1 for the first listed column.

You are not limited to referencing them in the exact order they were listed, like

GROUP BY 1, 2, 3

You can freely use the references in any order, like you would normally without using references.

GROUP BY 3, 1, 2

However, when using references, it is important to always keep in mind what numbers referenced which column, as it can become confusing as you list more columns in the SELECT. It is a convenient shortcut, but not necessarily always the best choice.

10 Likes

in the example:

SELECT ROUND(imdb_rating),
COUNT(name)
FROM movies
GROUP BY 1
ORDER BY 1;

Why we are using GROUP BY 1 twice?

1 Like

1 represents the column you are querying…i.e. imdb_rating
GROUP BY 1 means GROUP BY imdb_rating and
ORDER BY 1 means ORDER BY imdb_rating
Assuming you have more than one column in your query, then the preceding columns will be
numbered as well i.e. imdb_rating = 1, downloads = 2, price = 3, id =4

Another example below
SELECT imdb_rating, downloads, price, id
COUNT(name)
FROM movies
GROUP BY 2
ORDER BY 3;

Note that
GROUP BY 2 means GROUP BY downloads
ORDER BY 3 means ORDER BY price

7 Likes

Thanks for your explanation is very clear.
I understood the concept.

1 Like