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

Raffaele D'Alba 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
>
> 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,
>
> --
> Raffaele D'Alba

Look at using a subquery as part of your FROM clause.

Daniel A. Morgan Received on Wed May 23 2001 - 21:21:16 CDT