Re: Q: Avoiding group by on selected fields

From: jhy <jhy_at_earthling.net>
Date: 19 Aug 1998 15:29:39 GMT
Message-ID: <35DAEF62.57899FC3@earthling.net>

Try this:

select

    a.Field1,
    b.M_DATE_FIELD,
    a.Field2

from

   a_table a,
   (select

         field1,
         max(A_DATE_FIELD) M_DATE_FIELD,
     from
         a_table
     group by field1) b

where

   a.A_DATE_FIELD = b.M_DATE_FIELD;

Ivan Bajon wrote:

> I'm having problems formulating a group by statement. I'd like to stay clear
> of subqueries in this particular case - if so possible. I have a table:
>
> a_table:
> Field1,
> A_DATE_FIELD,
> Field2
>
> , and I wish to select rows in the following manner:
>
> All the rows, grouped by Field1, which has the maximum A_DATE_FIELD within
> each group - no matter the contents of Field2 - but I need Field2 as well
> for those rows satisfying the forementioned criteria.
>
> If I issue:
>
> select
> Field1,
> max(A_DATE_FIELD),
> Field2,
> from a_table
> group by Field1
>
> I get an error. If instead I issue:
>
> select
> Field1,
> max(A_DATE_FIELD),
> Field2,
> from a_table
> group by Field1, Field2
>
> I get too many rows because it groups the different valued Field2's as well.
> That's exactly what I'm trying to avoid. Is there a way to formulate this in
> SQL without the use of subqueries?
>
> Thanks to those who replied to my previous questions. I really hope you'll
> help me out this time too.
>
> - Ivan Bajon
Received on Wed Aug 19 1998 - 10:29:39 CDT