SAS: Proc Rank

data staff;
input ID SALARY Department;
datalines;
50 20000 1
10 18000 1
40 28000 2
20 16000 2
30 22000 3
70 26000 1
;run;

proc sort data=staff;by DEPARTMENT;run;

proc rank data=staff out=rankings;
By DEPARTMENT;
var SALARY;
ranks Finish;
run;

/* in Oracle or DB2 */

SELECT A.* FROM

(
SELECT ID, SALARY, DEPARTMENT,
RANK () OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC) AS RANK
FROM staff s1
) AS A

WHERE A.RANK=1