More Than One Analytical Function May Be Used in a Single Statement : Analytical Functions Basics « Analytical Functions « Oracle PL / SQL






More Than One Analytical Function May Be Used in a Single Statement


SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    ename              VARCHAR2(10 BYTE),   -- Employee Name
  4    hireDate          DATE,                -- Date Employee Hired
  5    orig_salary        Number(8,2),         -- Orignal Salary
  6    curr_salary        Number(8,2),         -- Current Salary
  7    region             VARCHAR2(1 BYTE)     -- Region where employeed
  8  )
  9  /

Table created.

SQL>
SQL> create table job(
  2    empno              Number(3)  NOT NULL, -- Employee ID
  3    jobtitle           VARCHAR2(10 BYTE)    -- Employee job title
  4  )
  5  /

Table created.

SQL> -- prepare data for employee table
SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(122,'Alison',to_date('19960321','YYYYMMDD'), 45000,       48000,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(123, 'James',to_date('19781212','YYYYMMDD'), 23000,       32000,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(104,'Celia',to_date('19821024','YYYYMMDD'), 53000,       58000,        'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(105,'Robert',to_date('19840115','YYYYMMDD'), 31000,      36000,        'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(116,'Linda', to_date('19870730','YYYYMMDD'), 43000,       53000,       'E')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(117,'David', to_date('19901231','YYYYMMDD'), 78000,       85000,       'W')
  3  /

1 row created.

SQL> insert into Employee(empno,  ename,  hireDate,                       orig_salary, curr_salary, region)
  2                values(108,'Jode',  to_date('19960917','YYYYMMDD'), 21000,       29000,       'E')
  3  /

1 row created.

SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
  2           values(101,   'Painter');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(122,   'Tester');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(123,   'Dediator');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(104,   'Chemist');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(105,   'Accountant');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(116,   'Manager');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(117,   'Programmer');

1 row created.

SQL>
SQL> insert into job(empno, jobtitle)
  2           values(108,   'Developer');

1 row created.

SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
Hit a key to continue

     EMPNO ENAME      HIREDATE  ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
       122 Alison     21-MAR-96       45000       48000 E
       123 James      12-DEC-78       23000       32000 W
       104 Celia      24-OCT-82       53000       58000 E
       105 Robert     15-JAN-84       31000       36000 W
       116 Linda      30-JUL-87       43000       53000 E
       117 David      31-DEC-90       78000       85000 W
       108 Jode       17-SEP-96       21000       29000 E

7 rows selected.

SQL> select * from job;
Hit a key to continue

     EMPNO JOBTITLE
---------- ----------
       101 Painter
       122 Tester
       123 Dediator
       104 Chemist
       105 Accountant
       116 Manager
       117 Programmer
       108 Developer

8 rows selected.

SQL>
SQL>
SQL> -- More Than One Analytical Function May Be Used in a Single Statement
SQL>
SQL>
SQL>
SQL> SELECT empno, ename, curr_salary,
  2    ROW_NUMBER() OVER(ORDER BY curr_salary) rnum,
  3    RANK() OVER(ORDER BY curr_salary) rank,
  4    DENSE_RANK() OVER(ORDER BY curr_salary) drank
  5  FROM employee
  6  ORDER BY ename;
Hit a key to continue

     EMPNO ENAME      CURR_SALARY       RNUM       RANK      DRANK
---------- ---------- ----------- ---------- ---------- ----------
       122 Alison           48000          4          4          4
       104 Celia            58000          6          6          6
       117 David            85000          7          7          7
       123 James            32000          2          2          2
       108 Jode             29000          1          1          1
       116 Linda            53000          5          5          5
       105 Robert           36000          3          3          3

7 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> drop table job;

Table dropped.

SQL>
SQL> -- clean the table
SQL> drop table Employee;

Table dropped.

SQL>
SQL>

           
       








Related examples in the same category

1.The direct application of a WHERE clause in the query is not allowed
2.Use multiple analytical functions
3.Combine analytical function with count(*)
4.Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)
5.Adding an Analytical Function to the GROUP BY with ORDER BY Version
6.Changing the Final Ordering after Having Added an Analytical Function
7.Use an analytical function in a WHERE clause
8.If more analytical functions are added, yet more sorting may result
9.Nulls could also be handled with a default value using the NVL function in the analytical function
10.Column alias for the analytical function is used in the final ORDER BY
11.See the difference between a person's salary and the average for his or her region