Re: trouble with sql
- From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- To: chris_stephens@xxxxxxxxxxxx
- Date: Wed, 3 Jan 2007 23:38:37 +0200
I'v created an example on dba_objects assuming col3 = object_name
grouped by col1 = owner, col2 = object_type
and the value of col4 = object_id
select mx, owner, object_type, object_id from (
select max(object_name) over (partition by owner, object_type) mx,
object_name, owner, object_type, object_id
from dba_objects)
where mx = object_name
order by 2, 3, 1
/
Keep in mind that you may have several rows with the same
max(object_name), owner, object_type and different object_id for
example for object type = TABLE PARTITION or INDEX PARTITION
Don't know whether this can affect somehow your real query :)
Gints Plivna
http://www.gplivna.eu
2007/1/3, Stephens, Chris <chris_stephens@xxxxxxxxxxxx>:
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?
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
