RANK() Analytic Function:
RANK calculates the rank of a value in a group of values. The return type is NUMBER.
If two or more rows tie for a rank, each tied rows receives the same rank. For example, if the two top employees have the same salary value, they are both ranked one. The employee with the next highest salary is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.
The sort order that is used for the whole query determines the order in which the rows appear in a result set.
PARTITION BY clause:
Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
ORDER BY clause:
Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window functioncalculation is performed.
Query used in Video:
select ename,sal,deptno,RANK() OVER(PARTITION BY deptno ORDER BY sal) from emp;
See here 6th row assigned the value '6',not '5'
because 4th and 5th rows have same value for salary. Its the same for 12 and 13 rows also.
we did not mention anything on OVER clause except ORDER BY.
If you want apply RANK() by department wise, we can use PARTITION BY clause.
PARTION BY works like GROUP BY in aggregates!