Oracle Partition By


Partition By is a construct in Oracle that groups data in a select clause rather than by using group by

Here's a PeopleSoft example. This SQL partitions mail addresses by EMPLID, EFFDT, EFF_STATUS and ADDR_TYPE and returns the maximum dated row for each partition.

select *
from
(
    select EMPLID, EFFDT, EFF_STATUS, ADDR_TYPE,
         max(EFFDT) OVER (partition by EFF_STATUS, ADDR_TYPE) as MAX_DATE
        from PS_ADDRESSES
        where ADDR_TYPE = 'MAIL'
) B
where EFFDT = B.MAX_DATE
and EFF_STATUS = B.EFF_STATUS;

Paritioning data is really useful for grouping data prior to manipulation (as opposed to group by which does grouping after manipulation). This is because group by is for grouping results while partition by is actually partition by the data and returning the appropriate partitioned data as the results.


Revision #1
Created Wed, Jul 24, 2019 6:17 PM by PeopleSoft Wiki
Updated Wed, Jul 24, 2019 6:19 PM by PeopleSoft Wiki