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?

2 Likes

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

11 Likes

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

1 Like

SELECT category,
price,
AVG(downloads(round 1))
FROM fake_apps
GROUP BY 1, 2;

In this example, I’m trying to get the price to round to 1 dp but it’s not working. Could anyone help please?

I am new to this, but try this and see if it is what you are going for:

SELECT category,

price,

ROUND(AVG(price),1)

FROM fake_apps

GROUP BY 1, 2;

1 Like

i interpreted the question in a way that i encountered my error, hopefully it will be useful for future peeps and or, give a fuller understanding of the SQL language

column references HAVE to follow the order based on the columns you listed in SELECT but listing columns based on the order of the table will result into an error