Prev: select/isolate group from... where max(date in group) = somedate?
Next: CTE syntax error problem....
From: Steve on 7 Jul 2008 12:41
seems you're making it harder than you have to. There's no need for a group
by or Max() funciont.

SELECT Code
FROM #Tmpa
WHERE CodeDate = '20081231'



"Rich" wrote:

> 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
From: Eric Isaacs on 7 Jul 2008 13:00
On Jul 7, 9:41 am, Steve <St...(a)discussions.microsoft.com> wrote:
> seems you're making it harder than you have to.  There's no need for a group
> by or Max() funciont.

But if you changed the date to '12/31/2007', his logic could find the
ones with a max of '12/31/2007' (code d) whereas your logic would find
any with '12/31/2007'.
 | 
Pages: 1
Prev: select/isolate group from... where max(date in group) = somedate?
Next: CTE syntax error problem....