Re: Q: Avoiding group by on selected fields

forgot the obvious: add a join on field1 to the query:

  and a.field1 = b.field1

jhy wrote:

> 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 - 16:14:30 CDT