Home -> Mailing Lists -> Oracle-L -> RE: why are these different..

RE: why are these different..

From: Elliott, Patrick <patrick.elliott_at_medtronic.com>
Date: Tue, 11 Mar 2008 16:25:31 -0500
Message-ID: <3B8B6A1700202C43A89D61CE495C894E0D7753B470@MSPM1BMSGM103.ent.core.medtronic.com> 





You need to add an order by for the entire query that matches the order by inside the firstvalue functions, otherwise the partitioning occurs in whatever order the rows are returned.
i.e


order by p.periodid, ps.projectnumber, ps.changedate desc




Pat







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:19 PM


To: Oracle-L Freelists


Subject: 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.



[CONFIDENTIALITY AND PRIVACY NOTICE]


Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records.
 


To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com


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


Received on Tue Mar 11 2008 - 16:25:31 CDT












Original text of this message