OLAP - Part 6 - Rank Function

Rank function:

• The rank function allows a column to be ranked either based on high or low order, against other rows in the answer set.

• By default, the output will be sorted in descending sequence of the ranking column(This is different from the other OLAP functions as all others have ascending as the default. This in short means that highest value in the ranked column gives rank 1.

• Syntax:

RANK(columnname).

where colname represents the column to be ranked and the descending sort key of the result.

• Example:

SELECT employeeid,salary,hiredate,RANK(salary) from employee;

Employeeid	Salary	Hiredate	Rank(Salary)
12	12000	8/1/1999	1	note that by default data is sorted on salary descending.
11	11000	5/1/1999	2
10	10000	1/1/1998	3
9	9000	4/1/1999	4
8	8000	1/1/1992	5
7	7000	6/1/1996	6
6	6000	3/1/1997	7
5	5000	2/1/1998	8
4	4000	1/1/1996	9
3	3000	1/1/1997	10
2	2000	1/1/1998	11
1	1000	1/1/1999	12

• Points to note are:
•  When Ranking is applied - default highest amount is low rank number.
•  The default sort sequence is descending by ranking column (sales). The result will be sorted by default on the column used for ranking and default sequence is descending.

• We can change the default sorting sequence as below

SELECT employeeid,salary,hiredate,RANK(salary ASC) from employee;

Here now the data is sorted on the salary by is in ascending sequence

Employeeid	Salary	Hiredate	Rank(Salary ASC)
1	1000	1/1/1999	1
2	2000	1/1/1998	2
3	3000	1/1/1997	3
4	4000	1/1/1996	4
5	5000	2/1/1998	5
6	6000	3/1/1997	6
7	7000	6/1/1996	7
8	8000	1/1/1992	8
9	9000	4/1/1999	9
10	10000	1/1/1998	10
11	11000	5/1/1999	11
12	12000	8/1/1999	12