Use multiple analytical functions : Analytical Functions Basics « Analytical Functions « Oracle PL / SQL






Use multiple analytical functions


SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(20 BYTE),
  4    Last_Name          VARCHAR2(20 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(20 BYTE),
  9    Description        VARCHAR2(80 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 2234.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 2324.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 3334.78, 'Vancouver','Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 4334.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 5334.78,'New York',  'Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 6334.78,'New York',  'Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 7334.78,'Vancouver', 'Tester')
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY                 DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- -------------------- --------------------------------------------------------------------------------
01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto              Programmer
02   Alison               Mathews              21-MAR-76 21-FEB-86    2234.78 Vancouver            Tester
03   James                Smith                12-DEC-78 15-MAR-90    2324.78 Vancouver            Tester
04   Celia                Rice                 24-OCT-82 21-APR-99    3334.78 Vancouver            Manager
05   Robert               Black                15-JAN-84 08-AUG-98    4334.78 Vancouver            Tester
06   Linda                Green                30-JUL-87 04-JAN-96    5334.78 New York             Tester
07   David                Larry                31-DEC-90 12-FEB-98    6334.78 New York             Manager
08   James                Cat                  17-SEP-96 15-APR-02    7334.78 Vancouver            Tester

8 rows selected.

SQL>
SQL>
SQL> -- multiple analytical functions
SQL>
SQL> SELECT id, first_name, salary,
  2    RANK() OVER(ORDER BY salary desc) toprank_orig,
  3      salary,
  4    RANK() OVER(ORDER BY Start_Date desc) toprank_curr
  5  FROM employee
  6  ORDER BY first_name;

ID   FIRST_NAME               SALARY TOPRANK_ORIG     SALARY TOPRANK_CURR
---- -------------------- ---------- ------------ ---------- ------------
02   Alison                  2234.78            7    2234.78            8
04   Celia                   3334.78            5    3334.78            6
07   David                   6334.78            2    6334.78            3
08   James                   7334.78            1    7334.78            1
03   James                   2324.78            6    2324.78            7
01   Jason                   1234.56            8    1234.56            2
06   Linda                   5334.78            3    5334.78            4
05   Robert                  4334.78            4    4334.78            5

8 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>
SQL>
SQL>
SQL>

           
       








Related examples in the same category

1.The direct application of a WHERE clause in the query is not allowed
2.Combine analytical function with count(*)
3.Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)
4.Adding an Analytical Function to the GROUP BY with ORDER BY Version
5.Changing the Final Ordering after Having Added an Analytical Function
6.Use an analytical function in a WHERE clause
7.More Than One Analytical Function May Be Used in a Single Statement
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