Thanks, that's helpful. My case is a little bit more complicated, because I use combination of fields for grouping, e.g. upper(ccode+town+street+...), so this could not be done in one select with subquery...

>I attempted some of the same queries mentioned in this thread against my own data. A lot of them didn't work. In the process I noticed that my own was wrong (what else is new :-))
>
>What did work:
>
>SELECT a.customer_name, b.*
>FROM customer a, address b
>where a.customer_id = b.customer_id and
>STR(b.customer_id, 19) + " " + TRANSFORM(b.effective_date) IN
>(SELECT MAX(STR(c.customer_id, 19) + " " + TRANSFORM(c.effective_date))
>FROM address c
>GROUP BY c.customer_id)
>
>In order to test the results, choose a customer that you know has two or more address records. If this works, you should be able to use MAX to see the most recent address and MIN to see the oldest address. I hope I didn't miss something in your posting (my usual mistakes.)
>
>I use STR() instead of TRANSFORM to ensure the length of the result. The space is used just to view the results.