select/isolate group from... where max(date in group) = so..

In the following data I need to select (or isolate) the codes (code groups)
which contain a max codeDate of '12/31/08'. Of the codes a, b, c, d -- only
codes b and c contain a max codeDate of '12/31/08' in their respective
groups. What would be the tsql to isolate these 2 codes (groups)?

create table #tmpA (code varchar(1), codeDate datetime)

insert into #tmpA
select 'a', '12/31/03'
union all select 'a', '12/31/04'
union all select 'a', '12/31/05'
union all select 'b', '12/31/04'
union all select 'b', '12/31/05'
union all select 'b', '12/31/06'
union all select 'b', '12/31/07'
union all select 'b', '12/31/08'
union all select 'c', '12/31/06'
union all select 'c', '12/31/07'
union all select 'c', '12/31/08'
union all select 'd', '12/31/04'
union all select 'd', '12/31/05'
union all select 'd', '12/31/06'
union all select 'd', '12/31/07'

Thanks,
Rich

Never mind. I figured it out:

select code from #tmpa
GROUP BY code HAVING max(codedate) = '12/31/08'

I just didn't have my thinking cap on this morning.


"Rich" wrote:

> In the following data I need to select (or isolate) the codes (code groups)
> which contain a max codeDate of '12/31/08'. Of the codes a, b, c, d -- only
> codes b and c contain a max codeDate of '12/31/08' in their respective
> groups. What would be the tsql to isolate these 2 codes (groups)?
>
> create table #tmpA (code varchar(1), codeDate datetime)
>
> insert into #tmpA
> select 'a', '12/31/03'
> union all select 'a', '12/31/04'
> union all select 'a', '12/31/05'
> union all select 'b', '12/31/04'
> union all select 'b', '12/31/05'
> union all select 'b', '12/31/06'
> union all select 'b', '12/31/07'
> union all select 'b', '12/31/08'
> union all select 'c', '12/31/06'
> union all select 'c', '12/31/07'
> union all select 'c', '12/31/08'
> union all select 'd', '12/31/04'
> union all select 'd', '12/31/05'
> union all select 'd', '12/31/06'
> union all select 'd', '12/31/07'
>
> Thanks,
> Rich