Difference between Rank() and Row_Number() functions

RANK ():- RANK returns ranking (order) of rows based on the number or expression given in the ORDER BY clause.

ROW_NUMBER ():- It returns the sequential number of the row within a group, starting with 1 based on the ORDER BY clause.

We will see the difference between these 2 functions based on an example:

EMPID	ENAME	SALARY	DEPT_ID	RNK	ROWNUM
1	MATT	1000	10	1	1
4	SHANE	1000	12	1	2
2	SCOTT	2000	10	3	3
7	MARY	2000	10	3	4
3	JEAN	2000	10	3	5
5	DEAN	4000	10	6	6
6	LISA	4000	10	6	7

Now, in a practical scenario, we are required to find the all the employees having Nth highest salary.

In such cases, we can use the below query:

SELECT

  E1

.EMPID

  ,

E1.ENAME

  ,

E1.SALARY

  ,

E1.DEPT_ID

  ,

RANK() OVER(ORDER BY E1.SALARY ASC) AS RNK

  ,

ROW_NUMBER() OVER(ORDER BY E1.SALARY ASC) AS ROWNUM

FROM

TL_62917_DLY1_QRMDB

.EMP1 AS E1

WHERE

(

E1.SALARY =

SELECT

SAL2.SALARY FROM

(

SELECT

SAL1.SALARY,SAL1.RNK FROM

(

SELECT

SALARY,RANK() OVER(ORDER BY SALARY DESC) RNK FROM TL_62917_DLY1_QRMDB.EMP1

GROUP

BY 1

)

SAL1

WHERE

SAL1.RNK= ?RNK

)

SAL2

)

Output for a value of ?RNK =2 will be:

EMPID	ENAME	SALARY	DEPT_ID	RNK	ROWNUM
3	JEAN	2000	10	1	1
2	SCOTT	2000	10	1	2
7	MARY	2000	10	1	3

This query will return Nth salary even if duplicate values are present in the Salary column.

If you look at the above table, 2000 is the 2nd highest salary.