rank() DENSE_RANK() over(patition by XX)

select 

RANK() over (order by sal desc),

rank() over (partition by deptno order by sal desc),

a.empno,a.deptno,

a.sal,

lag(sal,1) over (partition by deptno order by sal desc) - sal

 from emp a order by deptno,sal desc;

 RANK()OVER(ORDERBYSALDESC) RANK()OVER(PARTITIONBYDEPTNOORDERBYSALDESC)      EMPNO     DEPTNO        SAL LAG(SAL,1)OVER(PARTITIONBYDEPTNOORDERBYSALDESC)-SAL

-------------------------- ------------------------------------------- ---------- ---------- ---------- ---------------------------------------------------

                         2                                           1       7839         10       5000

                         7                                           2       7782         10       2450                                        2550

                         9                                           3       7934         10       1300                                        1150

                         3                                           1       7902         20       3000

                         3                                           1       7788         20       3000                                           0

                         5                                           3       7566         20       2975                                          25

                        12                                           4       7876         20       1100                                        1875

                        15                                           5       7369         20        800                                         300

                         1                                           1       7890         30       5600

                         6                                           2       7698         30       2850                                        2750

                         8                                           3       7844         30       1500                                        1350

                        10                                           4       7521         30       1250                                         250

                        10                                           4       7654         30       1250                                           0

                        12                                           6       7499         30       1100                                         150

                        14                                           7       7900         30        950                                         150