Re: trouble with sql

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_at_admworld.com>:
>
> 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.

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