CODEWITHSUNDEEP
sqlgroup-byoperator-precedence
rookieg
rookieg

Reputation: 81

SQL GROUP BY 1 2 3 and SQL Order of Execution

This may be a dumb question but I am really confused. So according to the SQL Query Order of Execution, the GROUP BY clause will be executed before the SELECT clause. However it allows to do something like:

SELECT field_1, SUM(field_2) FROM myTable GROUP BY 1

My confusion is that if GROUP BY clause happens before SELECT, in this scenario I provided, how does SQL know what 1 is? It works with ORDER BY clause and it makes sense to me because ORDER BY clause happens after SELECT.

Can someone help me out? Thanks in advance!

https://www.periscopedata.com/blog/sql-query-order-of-operations

Upvotes: 4

Views: 4145

Answers (3)

bat7
bat7

Reputation: 866

Each GROUP BY expression must contain at least one column that is not an outer reference. You cannot group by 1 if it is not a column in your table.

Upvotes: -1

travgm
travgm

Reputation: 131

My understanding is because it's ordinal notation and for the SELECT statement to pass syntax validation you have to have at least selected a column. So the 1 is stating the first column in the select statement since it knows you have a column selected.

EDIT:

I see people saying you can't use ordinal notation and they are right if you're using SQL Server. You can use it in MySQL though.

Upvotes: 2

Jo.........
Jo.........

Reputation: 330

select a,b,c from emp group by 1,2,3. First it will group by column a then b and c. It works based on the column after the select statement.

Upvotes: 2

Related Questions