Q: Avoiding group by on selected fields

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