-- auto-generated definition create table AB_EMPLOYEE ( EMP_ID varchar(15) null, EMP_NAME varchar(25) null, DEPT_ID varchar(15) null, EXPERTISE varchar(25) null, SALARY decimal null, RESULTS varchar(10) null ); INSERT INTO `AB_EMPLOYEE` VALUES ('5003','ABINASH','1','SCIENCE',50000,'PASS'),('5003','ABINASH','1','ENGLISH',50000,'PASS'),('5003','ABINASH','1','MATH',50000,'PASS'),('107','AMARESH','2','MATH',50000,'PASS'),('107','AMARESH','2','ENGLISH',50000,'PASS'),('105','JYOTI','3','MATH',75000,'FAIL'),('105','JYOTI','3','ENGLISH',75000,'PASS'),('7003','NISHAD','2','ENGLISH',70000,'FAIL'),('7003','NISHAD','2','MATH',70000,'PASS'),('6003','RAKESH','2','MATH',50556,'PASS'),('6003','RAKESH','2','ENGLISH',50556,'FAIL'),('104','RAVI','2','MATH',70000,'PASS'),('104','RAVI','2','ENGLISH',70000,'PASS'),('106','REDDY','2','MATH',80000,'FAIL'),('106','REDDY','2','ENGLISH',80000,'PASS');
select *, ROW_NUMBER() OVER(partition by DEPT_ID order by SALARY desc) as ROW_NUM, RANK() OVER(partition by DEPT_ID order by SALARY desc) as rank_num, DENSE_RANK() over(partition by DEPT_ID order by SALARY desc) as d_rank_num from AB_EMPLOYEE ORDER BY DEPT_ID,SALARY DESC