Oracle Analytic functions

Oracle Analytic functions

--row_number (): 1234567 consecutive repetitions

--rank (): Jump repeatable 12333678

--dense_rank (): Continuous repeatable 12333456

Row_number () over (partition by deptno ORDER BY Sal Desc)

Over (partition by [partition field, can have multiple] order by [one or more fields])

The written questions are as follows:

The topics are as follows:

Use an SQL statement to query the top three data for each department in the Scott.emp table, showing the following results:

DEPTNO SAL1 SAL2 SAL3

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

10 5000 2450 1300

20 3000 2975 1100

30 2850 1600 1500

Select deptno      ,max (SAL)   First name        Max (Decode (t                  ,2                  , Sal))   second       ,min (SAL)   third place   from  (select sal               ,deptno               ,t           from  (select empno                       ,ename                       ,sal     &nbSp;                 ,row_ Number ()  over (PARTITION BY DEPTNO ORDER BY SAL DESC)  t                        ,deptno                   FROM EMP)  e         where e.t  <= 3)  group by deptno;

Oracle Analytic functions