Re: How to get more details from a group by query.

A copy of this was sent to "Raffaele D'Alba" <raffaeledalba_at_libero.it> (if that email address didn't require changing) On Wed, 23 May 2001 20:30:08 GMT, you wrote:

>How to get more details from a group by query.
>
>consider two tables like:
>
>Object id_Object number,
> code varchar2(10)
>
>RevObj id_RevObj number,
> id_Object number,
> code varchar2(10),
> RevDate date
>
>linked with a foreign key in the column id_Object
>
>I need to select for each object the last revision, and with the date I need
>to obtain also all the data archived in the selected record included the
>id_RevObj and the code
>

select *
  from object a, revobj b
 where a.id_object = b.id_object
   AND b.revDate = ( select max(revDate)

                       from revobj c
                      where c.id_object = a.id_object )

>if I try with a query like this:
>
>select a.id_Object,a.code,max(b.RevDate) RevDate
>from Object a, RevObj b
>where a.id_Object=b.id_Object
>group by a.id_Object,a.code
>
>but to get the other details of RevObj I need to add an other level of joint
>with the table
>
>I thought to order the results by RevDate (desc) and add the where condition
>rownum=1, but this tecnique doesn't work because the query engine sorts the
>records only after the results of the where filter.
>
>I curretly use Oracle 7.3.4
>
>Anybody knows an other tecnique more simple ad direct?
>Thanks in advance,

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp