Re: 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 what you want is the maximun A_DATE_FIELD and the FIELD2 from the same row, you can do it...

SELECT field1,
max(to_char(a_date_field,'YYYYMMDD')||to_char(field2)) from a_table
group by field1

This will return a string with the max date (note the date format used to ensure this), concatenated with the FIELD2 value from the same row. Since you really want the date and the FIELD2 value in 2 columns, you need to select this column twice, with the right substr..

select field1,

     substr(max(to_char(a_date_field,'YYYYMMDD')||to_char(field2)) ,1,8) max_date,
substr(max(to_char(a_date_field,'YYYYMMDD')||to_char(field2)) ,9) max_field2_value
from a_table
group by field1

It looks a little hairy, but it works.

Dan Hekimian-Williams Received on Tue Aug 25 1998 - 21:12:27 CDT