Use of the OVER construct in SELECT CLAUSE

From: S. Kies <scott.kies_at_bestbuy.com>
Date: 20 Aug 2002 07:25:34 -0700
Message-ID: <eacbf730.0208200625.4097e12a@posting.google.com>

Hi,

I am working on updating a process someone wrote and I have no idea what the OVER phrase means. Can anyone give me a quick explanation.

TIA,
Scott

Example code:

   SELECT /*+ ordered full(jobs) full(h) use_nl(jobs ass)

         use_hash(h supass) no_expand
    */
   ass.location_id ass_loc,

  ass.assignment_id emp_assign,
  ass.object_version_number,
  ass.effective_start_date,
  ass.supervisor_id cur_sup_id,

  supass.location_id sup_loc,
  supass.assignment_id sup_assign,
  ass.organization_id emp_org,
  supass.organization_id sup_org,
  sup.person_id sup_id,
  ka.segment3 emp_cc,
  ks.segment3 sup_cc,

  jobs.employee_job_code,
  jobs.manager_job_code,
  jobs.same_cc,
  jobs.same_location,
   row_number() over (PARTITION BY ass.location_id, ass.assignment_id,
                supass.location_id, jobs.employee_job_code,
                jobs.manager_job_code
                ORDER BY supass.effective_start_date DESC,
                         supass.assignment_id
                         ) rr

Received on Tue Aug 20 2002 - 09:25:34 CDT