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

Each column that isn't a group function must be part of the GROUP BY clause. You have to add all the other detail columns to your GROUP BY clause. You may have to add an ORDER BY clause to get the output order you want.

> select a.id_Object,a.code,a.detail1,a.detail2...max(b.RevDate) RevDate
> from Object a, RevObj b
> where a.id_Object=b.id_Object
> group by a.id_Object,a.code,a.detail1,a.detail2...

Best!

Randall

Raffaele D'Alba <raffaeledalba_at_libero.it> wrote in message news:Z%UO6.19235$h9.1622758_at_news.infostrada.it...
> 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
>
>
Received on Sat Jul 21 2001 - 23:43:12 CEST