TSQL OVER CLAUSE That has no partition by has Order By clause

Question

I am having problems reading code like

SELECT 
    employeeID as ID,
    RANK() OVER (ORDER BY AVG (Salary) DESC) AS Value 
FROM Salaries 

which supposedly gets the average salary of every employees

My understanding is the code should be

SELECT 
    employeeID as ID,
    RANK() OVER (Partition By employeeID ORDER BY AVG (Salary) DESC) AS Value 
FROM Salaries 

but the above code works just fine?

Answer 1

First one is not working for me (returning Msg 8120 Column 'Salaries.employeeID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause), until I add group by employeeID:

SELECT 
    employeeID as ID,
    RANK() OVER (ORDER BY AVG (Salary) DESC) AS Value 
FROM Salaries
GROUP BY employeeID

Perhaps, for better understanding, it can be rewritten equivalently as:

;with cte as (
    SELECT employeeID, AVG (Salary) as AvgSalary
    FROM Salaries
    GROUP BY employeeID
)
select employeeID as ID
    , RANK() OVER (ORDER BY AvgSalary DESC) as Value
    --, AvgSalary
from cte

In this case, average salary by employee is calculated in the CTE, and then query is extended with ranking column Value. Adding partition by employeeID to over clause:

;with cte as (
    SELECT employeeID, AVG (Salary) as AvgSalary
    FROM Salaries
    GROUP BY employeeID
)
select employeeID as ID
    , RANK() OVER (partition by employeeID ORDER BY AvgSalary DESC) as Value
    --, AvgSalary
from cte

will lead to Value = 1 for every row in the result set (which is not what seem attempted to be achieved), because of rank() will reset numbering to 1 for each distinct employeeID, and employeeID is distinct in every row, since data was aggregated by this column prior to ranking.