Re: trouble with sql

  • From: Anthony Wilson <amwilson@xxxxxxxxxxxx>
  • To: chris_stephens@xxxxxxxxxxxx
  • Date: Thu, 4 Jan 2007 12:34:55 +0900

Hi Chris,

This is exactly what the FIRST and LAST aggregate functions are for. The syntax
is ... not immediately obvious (to put it nicely), but the functions are
specifically designed for this problem.  I've made a few assumptions about your
table structure below (such as non-nullable ps.changeDate), but hopefully you
get the idea:

select p.periodID
, ps.projectNumber
, max(ps.changeDate) as changeDate
, max(ps.statusID) keep (
   dense_rank first
   order by ps.changeDate desc
) as statusID
from period p
, projectStatus ps
where ps.changeDate >= trunc(p.startDate)
and trunc(ps.changeDate) <= p.endDate
group by p.periodID
, ps.projectNumber

To paraphrase, the query says:
1. Group by the p.periodID and ps.projectNumber.
2. For each group, return the max ps.changeDate as changeDate.
3. For each group, also order the rows within the group by the ps.changeDate
descending.  Rank these rows using the dense_rank function and keep the SET of
rows which rank first by the given sorting specification (if the grouping fields
are indeed a primary / unique key, this set consists of only one row).
4. Return the maximum ps.statusID from this set.

I find these functions extremely useful, as this is a requirement which often
pops up... no more self-joins!

Hope that helps.


Quoting "Stephens, Chris" <chris_stephens@xxxxxxxxxxxx>:

> There has got to be a way to do this but I can't find the syntax. 

> Essentially I need the max of column 3 grouped by column 1 and 2 and
> also the value of column 4 that corresponds to the returned columns 1,2,
> and 3.
> Here is the actual sql that I believe to be using an unnecessary join:
> select ps2.periodID, ps2.projectNumber, ps2.changeDate, ps3.statusID
> from ( select p.periodID, ps.projectNumber, max(ps.changeDate) as
> changeDate
>  from period p, projectStatus ps
>  where ps.changeDate >= trunc( p.startDate )
>        and trunc( ps.changeDate ) <= p.endDate
>  group by p.periodID, ps.projectNumber ) ps2, projectStatus ps3
> where ps2.projectNumber = ps3.projectNumber and ps2.changeDate =
> ps3.changeDate
> Is there a better way to write this?

Other related posts: