In Oracle : Partition by in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle SQL & PL/SQL

Oracle SQL Oracle PL/SQL

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle SQL & PLSQL

-> Partition by in Oracle

 

 Partition by in Oracle

 

 

NOTE: I named "group by" column a column based on a function like SUM, COUNT, AVG, MAX, MIN (demands a "group by" clause in the SELECT statement ).

 

This can be done with the over() option:

Exemples:

 

select max(comm) over() as max_comm, empno, ename, comm from emp;

 

select empno, ename, max(sal) over() as max_sal, sal from emp;

 

 

 

SQL> select max(comm) from emp;

MAX(COMM)
-----------------
1400

 

SQL> select max(comm) over() as max_comm, empno, ename, comm from emp;

MAX_COMM   EMPNO   ENAME    COMM
----------            ----------  ----------   ----------
1400                   1               SMITH
1400                   2               ALLEN        300
1400                   3               WARD         500
1400                   4               JONES
1400                   5               MARTIN    1400
1400                   6               BLAKE
1400                   7               CLARK
1400                   8               SCOTT
1400                   9               KING
1400                 10               TURNER       10
1400                 11               ADAMS
1400                 12               JAMES
1400                 13               FORD
1400                 14               MILLER

 

 

You can also calculate a MAX per group:

 

select empno, ename, job, max(sal) over(partition by job ) as max_sal, sal from emp;

 

 

 EMPNO    ENAME      JOB                MAX_SAL         SAL
----------    ----------    ---------           ----------           ----------
  8                 SCOTT       ANALYST    3000                  3000
13                 FORD         ANALYST    3000                  3000

14                 MILLER     CLERK          1300                  1300
12                 JAMES       CLERK          1300                    950
  1                 SMITH       CLERK          1300                    999
11                 ADAMS     CLERK          1300                  1100
  6                 BLAKE       MANAGER   2975                  2850
  4                 JONES        MANAGER   2975                  2975
  7                 CLARK       MANAGER   2975                  2450

  9                 KING          PRESIDENT  5000                  5000
10                 TURNER     SALESMAN 1600                       12
  5                 MARTIN     SALESMAN 1600                   1250
  3                 WARD         SALESMAN 1600                   1250
  2                 ALLEN        SALESMAN 1600                   1600

 

 

 

In-Oracle.com  -> Oracle SQL & PLSQL

-> Partition by in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.