TSQL Max and Over Clause
You can use the MIN, MAX, AVG and COUNT functions with the OVER clause to provide aggregated values on multiple rows. For instance: SELECT Name , MIN ( Rate ) OVER ( PARTITION BY edh . DepartmentID ) AS MinSalary , MAX ( Rate ) OVER ( PARTITION BY edh . DepartmentID ) AS MaxSalary , AVG ( Rate ) OVER ( PARTITION BY edh . DepartmentID ) AS AvgSalary , COUNT ( edh . BusinessEntityID ) OVER ( PARTITION BY edh . DepartmentID ) AS EmployeesPerDept FROM EmployeePayHistory AS eph JOIN EmployeeDepartmentHistory AS edh ON eph . BusinessEntityID = edh . BusinessEntityID JOIN Department AS d ON d . DepartmentID = edh . DepartmentID http://msdn.microsoft.com/en-us/library/ms187751.aspx