The syntax for the RANK function currently states:
RANK () OVER ( ORDER BY expression [ ASC | DESC ] )
The syntax should include the [PARTITION BY] clause:
RANK () OVER ( [ PARTITION BY ] ORDER BY expression [ ASC | DESC ] )
The example should be:
SELECT Surname, Sex, Salary, RANK() OVER (PARTITION BY Sex ORDER BY Salary DESC) AS RANK FROM Employees WHERE State IN ('CA', 'AZ') AND DepartmentID IN (200, 300) ORDER BY Sex, Salary DESC;
The results from the above query:
Surname Sex Salary RANK ------- --- ------ ---- Savarino F 72300.000 1 Jordan F 51432.000 2 Clark F 45000.000 3 Coleman M 42300.000 1 Overbey M 39300.000 2