More Than One Analytical Function May Be Used in a Single Statement ROW_NUMBER(), RANK(), DENSE_RANK() : ROW_NUMBER « Analytical Functions « Oracle PL / SQL

Oracle PL / SQL
1. Aggregate Functions
2. Analytical Functions
3. Char Functions
4. Constraints
5. Conversion Functions
6. Cursor
7. Data Type
8. Date Timezone
9. Hierarchical Query
10. Index
11. Insert Delete Update
12. Large Objects
13. Numeric Math Functions
14. Object Oriented Database
15. PL SQL
16. Regular Expressions
17. Report Column Page
18. Result Set
19. Select Query
20. Sequence
21. SQL Plus
22. Stored Procedure Function
23. Subquery
24. System Packages
25. System Tables Views
26. Table
27. Table Joins
28. Trigger
29. User Previliege
30. View
31. XML
Java
Java Tutorial
Java Source Code / Java Documentation
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL/SQL Tutorial
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Oracle PL / SQL » Analytical Functions » ROW_NUMBER 
More Than One Analytical Function May Be Used in a Single Statement ROW_NUMBER(), RANK(), DENSE_RANK()
 


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(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  /

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  /

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  /

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  /

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  /

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  /

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  /

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

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

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

rows selected.

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_salaryrnum,
  3    RANK() OVER(ORDER BY curr_salaryrank,
  4    DENSE_RANK() OVER(ORDER BY curr_salarydrank
  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

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. ROW_NUMBER(): return a number with each row in a group, starting at 1
2. ROW_NUMBER function with an ordering on salary in descending order
3. ROW_NUMBER() with order in descending order
4. Rank() with nulls last
5. row_number over partition by and order by
6. Sum over and row_number() over
7. Decode the result from row_number over, partition by, order by
8. Open a cursor created by using row_number function
9. Create a view based on row_number function
10. row_number() over (partition by deptno order)
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.