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.
No Comments