Making Use of RANK and ROW

Making Use of RANK and ROW_NUMBER

Introduction
row_number is an analytical function that can be used to generate serial numbers in SQL queries partition wise. This function can be used in transforming a number of queries for many complex business requirements.

Pagination

row_number can be used for pagination queries in web based applications

select deptno, ename, salary from(
select deptno, ename, salary, row_number() over(partition by deptno order by salary desc) r1 from employees
) where r1 between 20 and 30 order by r1;

Highest Salary in each department

For selecting the employee with the highest salary in each department, the following group by query can be used

select deptno, ename, salary from employees a where salary=(select max(salary) from employees b where a.deptno=b.deptno);

The above query can be rewritten using Using row_number or rank or dense_rank function

select deptno, ename, salary from(
select deptno, ename, salary, row_number() over(partition by deptno order by salary desc) r1 from employees
) where r1=1;

If there are more than one employee in each department having the same highest salary the use dense_rank instead of row_number.

This method can be used as solution for TOP N query problems.

Highest Salary and Location criteria

Modifying the query for a different business requirement
that the query should not return any result if anybody in the department is at onsite.

select deptno, ename, salary from(
select deptno, ename, salary, row_number() over(partition by deptno order by salary desc) r1,
row_number() over(partition by deptno order by case when location='ONSITE' then 0 else 1 end, salary DESC) g1
from employees
) where r1=1;