RANKING FUNCTIONS IN SQL SERVER

Dept Table

Ranking function

These are built in functions that are used to generate rank for the row based on the values of a column in the table.

Ranking functions must  use over “order by clause”

Ranking functions available in sql server are as follows

Rank()

Dense_Rank()

Row_number()

Rank()-It is used to generate the rank by skipping next rank when more than one row share the same rank

dense_rank() - It is used to generate the rank, and it generates sequential no for the rows in same order, as they are retrieved from the table.

Row_number()

This will not generate a rank and it generates sequential number for the rows in same order as they are retrieved from the table.

rank  dense_rank

1      1

2      2

2      2

4      3

4     3

4     3

7     4

Display empno, empname ,salary and deptno along with the rank for the employee based on the sal by giving the salary  one for the highest salary of the emp deptwise

select empno,ename,sal ,deptno,rank() over(partition by deptno order by sal desc) as rank,

DENSE_RANK() over(partition by deptno order by sal desc) as denserank,

ROW_NUMBER() over(partition by deptno order by sal desc) as rownum from emp

Output:

Display empno,ename and salaries along with the rank for the employee based on the salary of the employee with the highest salary employee given rank1

select empno,ename,sal ,deptno,rank() over(order by sal desc) as rank,

DENSE_RANK() over(order by sal desc) as denserank,

ROW_NUMBER() over(order by sal desc) as rownum from emp

Output:

7839   KING   5000.00 10  1   1   1

7902   FORD   3000.00 20  2   2   2

7788   SCOTT  3000.00 20  2   2   3

7566   JONES  2975.00 20  4   3   4

7698   BLAKE  2850.00 30  5   4   5

7782   CLARK  2450.00 10  6   5   6

7499   ALLEN  1600.00 30  7   6   7

7844   TURNER 1500.00 30  8   7   8

7934   MILLER 1300.00 40  9   8   9

7521   WARD   1250.00 30  10  9   10

7654   MARTIN 1250.00 30  10  9   11

7876   ADAMS  1100.00 20  12  10  12

7900   JAMES  950.00 30  13  11  13

7369   SMITH  800.00 20  14  12  14

While using the ranking functions within the order clause you can use partition by along with order by for dividing the rows of the table into partitions and generate rows for each partition separately and in this case partition must be written first and then order by.

Note: dense_Rank function won’t generate gaps

Rank function generate gaps

Display ename,salary with rank ranking should be based on salary?

select ename,sal,rank() over(order by sal desc) as rank from emp

select ename,sal,dense_Rank() over(order by sal desc) as denserank from emp

Based on the salary first then rank should be on hire date

select ename, sal ,DENSE_RANK() over(order by sal desc,hiredate) as rank  from emp

Find the rank of the employee with in the dept based on their salaries

Select ename, sal, deptno, dense_Rank() over(partition by deptno order by sal desc) as rank from emp