I found a successful, albeit kludgy, workaround. Add a dummy column to EmployeeReview table populated with 1. A join on it behaves like a cross join, but is executed like an inner join. Not an ideal implementation, but it works.

SELECT

Employees.EmployeeID,

Employees.[% of Positive],

SUM(CASE WHEN Employees.[% of Positive] < Groups.[% of Positive] THEN 1 ELSE 0 END) + 1 [Rank]

FROM

(

SELECT

JoinKey,

EmployeeReview.EmployeeID,

CAST(SUM(IsPositive) AS DECIMAL(19,5)) / CAST(SUM(1) AS DECIMAL(19,5)) AS [% of Positive]

FROM

dbo.EmployeeReview JOIN

dbo.Employee ON EmployeeReview.EmployeeID = Employee.EmployeeID

WHERE

GroupID = 1

GROUP BY

JoinKey,

EmployeeReview.EmployeeID

) Employees

JOIN

(

SELECT

JoinKey,

GroupID,

CAST(SUM(IsPositive) AS DECIMAL(19,5)) / CAST(SUM(1) AS DECIMAL(19,5)) AS [% of Positive]

FROM

dbo.EmployeeReview JOIN

dbo.Employee ON EmployeeReview.EmployeeID = Employee.EmployeeID

GROUP BY

JoinKey,

GroupID

) Groups ON Employees.JoinKey = Groups.JoinKey

GROUP BY

Employees.EmployeeID,

Employees.[% of Positive]