why are these different..
Date: Tue, 11 Mar 2008 16:05:54 -0500
Message-ID: <7070047601C21A4CB387D50AD3661F6E093E8CEC@050EXCHANGE.research.na.admworld.com>
This might show my lack of sql understanding but I'm trying to rewrite a
query to prevent an extra scan of a table. The original query looks
like:
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
my attempt to rewrite:
select distinct p.periodid,
ps.projectnumber,
first_value(ps.changedate) over (partition by p.periodid, ps.projectnumber order by ps.changedate desc) changedate,
first_value(ps.statusid) over (partition by p.periodid, ps.projectnumber order by ps.changedate desc) statusid
from period p,
projectstatus ps
where ps.changedate >= trunc(p.startdate) and
trunc( ps.changeDate ) <= p.endDate
the results are different though.
Anyone see why they are different?
CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this
communication in error, please notify us immediately by email reply.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 11 2008 - 16:05:54 CDT