why are these different..

From: Stephens, Chris <chris_stephens_at_admworld.com>
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,


       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?    

        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.

Received on Tue Mar 11 2008 - 16:05:54 CDT

Original text of this message