Rank, Dense_Rank & Row_number:-

To understand Rank, Dense_Rank and Row_number Analytical functions, let’s first create a table:-

CREATE TABLE EMPLOYEE (NAME VARCHAR2 (10), ADDRESS VARCHAR2 (10), SALARY NUMBER);

INSERT INTO EMPLOYEE VALUES ('CHANCHAL','KALYAN',70);

INSERT INTO EMPLOYEE VALUES ('PREETI','THANE',80);

INSERT INTO EMPLOYEE VALUES ('HARSHAD','MUMBAI',60);

INSERT INTO EMPLOYEE VALUES ('BHAVIN','DADAR',60);

INSERT INTO EMPLOYEE VALUES ('GANESH','KURLA',50);

INSERT INTO EMPLOYEE VALUES ('POOJA','VASHI',70);

INSERT INTO EMPLOYEE VALUES ('MANGESH','VIRAR',90);

INSERT INTO EMPLOYEE VALUES ('JAMES','LONDON',90);

INSERT INTO EMPLOYEE VALUES ('JIM','NY',80);

commit;

RANK () Function:-

Returns the rank of each row in the result set of partitioned column.

SELECT NAME,ADDRESS,SALARY,

RANK() OVER(ORDER BY SALARY DESC)RANK

FROM EMPLOYEE;

DENSE_RANK () Function:-

This is same as rank () function. Only difference is returns rank without gaps.

SELECT  NAME,ADDRESS,SALARY,

DENSE_RANK () OVER (ORDER BY SALARY DESC) RANK

FROM EMPLOYEE;

In rank () result set screenshot, you can notice that there is gap in rank (2). 

ROW_NUMBER() Function:-
 

Returns the serial number of the row order by specified column.

SELECT NAME,ADDRESS,SALARY,

ROW_NUMBER() OVER(ORDER BY NAME) ROWNUMBER

FROM EMPLOYEE;

Now let's see the difference between Rank, Dense_Rank and Row_Number function:-

SELECT NAME,ADDRESS,SALARY,

RANK () OVER (ORDER BY SALARY DESC) RANK,

DENSE_RANK () OVER (ORDER BY SALARY DESC) DENSE_RANK,

ROW_NUMBER () OVER (ORDER BY SALARY DESC) ROW_NUMBER

FROM EMPLOYEE;

Now, you can see the difference above in the three functions.

Read Also:- Creating Sequence