RE: why are these different..

Part of the problem is that there are multiple statusid's associated with a periodid/projectnumber/changedate combo. ...but there is a different number of rows being returned that I don't understand.  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stephens, Chris Sent: Tuesday, March 11, 2008 4:06 PM
To: Oracle-L Freelists
Subject: why are these different..  

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.

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