Using Rank() and DENSE_RANK() and ROW_NUMBER() functions:

Table and data creation:

CREATE TABLE emptest

(eno VARCHAR2(20) primary key,ename VARCHAR2(20),salary NUMBER(10,2)

);COMMIT;

insert into emptest values('E101','Ravi Sambangi','3000');

insert into emptest values('E102','Gladiator','3600');

insert into emptest values('E222','Hellboy','4500');

insert into emptest values('E345','Spider Man','3100');

insert into emptest values('E631','Ravi Sambangi','3300');

insert into emptest values('E761','Harry Potter','3300');

commit;

View Data:

SELECT * FROM emptest;

ENO	ENAME	SALARY
E101	Ravi Sambangi	3000
E102	Gladiator	3600
E222	Hellboy	4500
E345	Spider Man	3100
E631	Ravi Sambangi	3300
E761	Harry Potter	3300

Scenario #1: Display the ranks for each record based on ename. If more than one records is having same ename, it assigns different rank to each record.

SELECT eno,ename,salary,rank from (select eno,ename,salary,RANK() OVER(PARTITION BY ename ORDER BY salary desc NULLS LAST) AS Rank FROM emptest)

ENO	ENAME	SALARY	RANK
E102	Gladiator	3600	1
E761	Harry Potter	3300	1
E222	Hellboy	4500	1
E631	Ravi Sambangi	3300	1
E101	Ravi Sambangi	3000	2
E345	Spider Man	3100	1

Scenario #2: Display the ranks for each record based on salary. If more than one records is having same salary, it assigns different rank to each record.

SELECT eno,ename,salary,rank from (select eno,ename,salary,RANK() OVER(ORDER BY salary desc NULLS LAST) AS Rank FROM emptest)

If same rank(eg:rank 3) is assigned to say 2 records, then next rank number will be 5 only. If you would like to have continuous number use DENSE_RANK(). See Scenario#3.

ENO	ENAME	SALARY	RANK
E222	Hellboy	4500	1
E102	Gladiator	3600	2
E761	Harry Potter	3300	3
E631	Ravi Sambangi	3300	3
E345	Spider Man	3100	5
E101	Ravi Sambangi	3000	6

Scenario #3: Display the ranks for each record based on salary. If more than one records is having same salary, it assigns different rank to each record.

SELECT eno,ename,salary,rank from (select eno,ename,salary,DENSE_RANK() OVER(ORDER BY salary desc NULLS LAST) AS Rank FROM emptest)

ENO	ENAME	SALARY	RANK
E222	Hellboy	4500	1
E102	Gladiator	3600	2
E761	Harry Potter	3300	3
E631	Ravi Sambangi	3300	3
E345	Spider Man	3100	4
E101	Ravi Sambangi	3000	5

Note: Even though the same rank(eg:rank 3) is assigned to say 2 records, the next rank number is immediate number.

Scenario #4: Display all the records from the emptest table with the below constrains

1. If more than one employee having the same name, display only one employee information who is getting more salary.

SELECT eno,ename,salary,rank from (select eno,ename,salary,RANK() OVER(PARTITION BY ename ORDER BY salary desc NULLS LAST) AS Rank FROM emptest) where rank=1

ENO	ENAME	SALARY
E102	Gladiator	3600
E761	Harry Potter	3300
E222	Hellboy	4500
E631	Ravi Sambangi	3300
E345	Spider Man	3100

Scenario #5:

Display the employee record who is getting 3rd highest salary (Using RANK())

There could be some risk using RANK() function in below queries.

SELECT eno,ename,salary,rank from (select eno,ename,salary,RANK() OVER(ORDER BY salary desc NULLS LAST) AS Rank FROM emptest) where rank=3

ENO	ENAME	SALARY	RANK
E631	Ravi Sambangi	3300	3
E761	Harry Potter	3300	3

Display the employee record who is getting 4th highest salary.

SELECT eno,ename,salary,rank from (select eno,ename,salary,RANK() OVER(ORDER BY salary desc NULLS LAST) AS Rank FROM emptest) where rank=4

ENO

ENAME

SALARY

RANK

No Records Found

Reason: RANK() function won't have continuous numbers sometimes. Refer Scenario#2 output to see the ranks allocated to all the rows.

To escape from above problems, use DENSE_RANK() which will have continuous numbers. See Scenario#3 for more details on DENSE_RANK()

Scenario #6:

Display the employee record who is getting 3rd highest salary (Using DENSE_RANK())

SELECT eno,ename,salary,rank from (select eno,ename,salary,DENSE_RANK() OVER(ORDER BY salary desc NULLS LAST) AS Rank FROM emptest) where rank=3

ENO	ENAME	SALARY	RANK
E631	Ravi Sambangi	3300	3
E761	Harry Potter	3300	3

Got the same result as RANK(), but below query brings different result than RANK()

Display the employee record who is getting 4th highest salary (Using DENSE_RANK())

SELECT eno,ename,salary,rank from (select eno,ename,salary,DENSE_RANK() OVER(ORDER BY salary desc NULLS LAST) AS Rank FROM emptest) where rank=4

ENO	ENAME	SALARY	RANK
E345	Spider Man	3100	4

Got the 4th highest salary records but if we use RANK() we will not get it.

Additional Item:

Assume you have another record in your database table say: insert into emptest values('E632','Ravi Sambangi','3300');

If multiple records are having same name then, consider only one record based on highest salary. If more than one record is having same name and same highest salary for that name, just consider only one record, then we have to use ROW_NUMBER() function.

SELECT eno,ename,salary,rank from (select eno,ename,salary,ROW_NUMBER() OVER(PARTITION BY ename ORDER BY salary desc NULLS LAST) AS Rank FROM emptest) where rank=1;

EID	ENAME	SALARY	RANK
E102	Gladiator	3600	1
E761	Harry Potter	3300	1
E222	Hellboy	4500	1
E631	Ravi Sambangi	3300	1
E345	Spider Man	3100	1