Analytic Functions and Miscellaneous

RANK and DENSE_RANK both provide rank to the records based on some column value or expression.In case of a tie of 2 records at position N,RANK declares 2 positions N and skips position N+1 and gives position N+2 to the next record.While DENSE_RANK declares 2 positions N but does not skip position N+1. Dense_rank avoids skipping ranks when there is a tie.
ROW_NUMBER( ) gives a running serial number to a partition of records.It avoids the duplicate ranks.

EX:For the given sal as below,basing on ascending order of salaries,rank,dense_rank and row_number are as follows:

SAL RANK DENSE_RANK ROW_NUMBER
1000 1 1 1
3000 2 2 2
3000 2 2 3
6000 4 3 4
9000 5 4 5


LEAD has the ability to compute an expression on the next rows (rows which are going to come after the current row) and return the value to the current row.
LEAD provides access to a row at a given physical offset beyond that position.
EX:SELECT ename,hire_date,LEAD(hire_date, 1) OVER (ORDER BY hire_date) from emp;
Output gives the hire date of the employee hired just after,for each employee in emp table.

LAG provides access to a row at a given physical offset prior to that position.
EX:select ename,hire_date,salary,LAG(salary, 1, 0) OVER (ORDER BY hire_date) from emp;
Output gives the salary of the employee hired just before,for each employee in emp table.

FIRST_VALUE analytic function picks the first record from the partition after doing the ORDER BY.It returns the first value in an ordered set of values.If the first value in the set is null, then the function returns NULL unless you specify IGNORE NULLS.
EX:SELECT ename,salary,FIRST_VALUE(last_name) OVER (ORDER BY salary ASC) FROM emp;
Output gives the name of the employee with the lowest salary for all the employees.

LAST_VALUE function acts on the last record of the partition. It returns the last value in an ordered set of values. If the last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS.
EX:select ename,salary,hire_date,LAST_VALUE(hire_date) OVER (ORDER BY salary) from emp;
Output gives the hire date of the employee earning the highest salary,for all employees in emp table.