Re: trouble with sql

From: Anthony Wilson <amwilson_at_iinet.net.au>
Date: Thu, 4 Jan 2007 12:34:55 +0900
Message-ID: <1167881695.459c75df72136@mail.iinet.net.au>

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.

cheers,
Anthony

Quoting "Stephens, Chris" <chris_stephens_at_admworld.com>:

> 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?

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 03 2007 - 21:34:55 CST