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.
