Joseph Michael Pesch
VP Programming

Oracle Common Table Expression, Oracle Partition

by 3. March 2010 16:23

Common table expression allows "with" statement to assign a temporary table name to a select statement (similar to SQL server "using" statement).  Also, the below example shows how to use the Oracle "partition" function to group results (this sample shows how to get the first row of each group of data).

with MaxStatus as
(
  select * from
  (
    select
      h.*
    , row_number() over (partition by h.ap_num order by h.change_date) as RowNumber
    from history h
  ) where RowNumber = 1
)
select * from MaxStatus

Tags:

Oracle

Comments are closed