PeopleSoft Wiki

Partition By is a construct in SQL (available only in Oracle as far as I know) 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 grouping resulting data while partition by is actually partition by the data and returning the appropriate partitioned data.