sql - Oracle - where the orderly column ID e.g. ORDER BY 1 is allwed?

Question

Question

What are the rules where the orderly column ID 1, 2, ... is allowed? From which part of document can I tell?

SELECT *
FROM (
    SELECT Months * Salary, COUNT(*)
    FROM Employee
    GROUP BY (Months * Salary) 
    ORDER BY 1 DESC     <---- This is OK
    )
WHERE ROWNUM = 1;

----------
108064 7

SELECT *
FROM (
    SELECT Months * Salary, COUNT(*)
    FROM Employee
    GROUP BY 1        <--- ORA-00979: not a GROUP BY expression
    ORDER BY 1 DESC
    )
WHERE ROWNUM = 1;

----------
SELECT Months * Salary, COUNT(*)
*
ERROR at line 3:
ORA-00979: not a GROUP BY expression

For this case, which document can tell it is not allowed and why?

• group_by_clause::=

• order_by_clause::=

question from:https://stackoverflow.com/questions/65876532/oracle-where-the-orderly-column-id-e-g-order-by-1-is-allwed

Answer 1

• In the GROUP BY clause the 1 is a number literal value.
• In the ORDER BY clause the 1 refers to the the first term of the SELECT clause.

If you do:

SELECT *
FROM (
    SELECT COUNT(*)
    FROM Employee
    GROUP BY 1              -- A number literal
    ORDER BY 1 DESC
    )
WHERE ROWNUM = 1;

It is the same as:

SELECT *
FROM (
    SELECT COUNT(*)
    FROM Employee
    GROUP BY NULL           -- A NULL literal
    ORDER BY 1 DESC
    )
WHERE ROWNUM = 1;

or

SELECT *
FROM (
    SELECT COUNT(*)
    FROM Employee
    GROUP BY 'ABC'          -- A string literal
    ORDER BY 1 DESC
    )
WHERE ROWNUM = 1;

However,

SELECT *
FROM (
    SELECT Months * Salary, COUNT(*)
    FROM Employee
    GROUP BY 1
    ORDER BY 1 DESC
    )
WHERE ROWNUM = 1;

Is not valid as 1 is a literal number value that you are grouping by whereas Months and Salary are column names that are in a GROUP BY query but are not aggregated.