SQL – Row Number, Rank, Dense Rank

— Create Employee Table

  CREATE TABLE Employee
  (EmployeeID smallint NOT NULL,
  Name nvarchar(50) NOT NULL,
  DeptID smallint NULL,
  Salary integer NULL
  );

— Insert data

  INSERT INTO Employee(EmployeeID,Name,DeptID,Salary)
  VALUES(1,’Mia’,’5′,50000);
  INSERT INTO Employee(EmployeeID,Name,DeptID,Salary)
  VALUES(2,’Adam’,’2′,50000);
  INSERT INTO Employee(EmployeeID,Name,DeptID,Salary)
  VALUES(3,’Sean’,’5′,60000);
  INSERT INTO Employee(EmployeeID,Name,DeptID,Salary)
  VALUES(4,’Robert’,’2′,50000);
  INSERT INTO Employee(EmployeeID,Name,DeptID,Salary)
  VALUES(5,’Jack’,’2′,45000);
  INSERT INTO Employee(EmployeeID,Name,DeptID,Salary)
  VALUES(6,’Neo’,’5′,60000);
  INSERT INTO Employee(EmployeeID,Name,DeptID,Salary)
  VALUES(7,’Jennifer’,’2′,55000);
  INSERT INTO Employee(EmployeeID,Name,DeptID,Salary)
  VALUES(8,’Lisa’,’2′,85000);
  INSERT INTO Employee(EmployeeID,Name,DeptID,Salary)
  VALUES(9,’Martin’,’2′,35000);
  INSERT INTO Employee(EmployeeID,Name,DeptID,Salary)
  VALUES(10,’Terry’,’5′,90000);
  INSERT INTO Employee(EmployeeID,Name,DeptID,Salary)
  VALUES(12,’David’,’5′,60000);

— RowNumber, Rank and Dense_Rank

  Select EmployeeID, Name, DeptID, Salary ,
  Row_Number() OVER (PARTITION BY DEPTID ORDER BY SALARY) AS RANK1
  ,RANK() OVER (PARTITION BY DEPTID ORDER BY SALARY) AS RANK2
  ,DENSE_RANK() OVER (PARTITION BY DEPTID ORDER BY SALARY) AS RANK3
  FROM EMPLOYEE;