I have a table the looks something like:
c1 c2 c3
1 3 1/1/2005
1 1 2/1/2005
1 5 4/1/2005
1 2 9/1/2005

I want to Group By c1 select the the last c2 value based on Max(c3); which,
is c2=2 and c3=9/1/2005. I'm sure I've done this before, but can't figure it
out.

Thanks,
Rob

Try,

select c1, c2, c3
from table1 as t1
where c3 = (select max(t2.c3) from table1 as t2 where t2.c1 = t1.c1)


AMB

"Robert Hamilton" wrote:

So maybe something like


So it would be something similar to

USE TempDB
GO
CREATE TABLE calc (c1 int, c2 int, c3 smalldatetime)

INSERT calc VALUES(1, 3 , '1/1/2005')
INSERT calc VALUES(1, 1 , '2/1/2005')
INSERT calc VALUES(1, 5 , '4/1/2005')
INSERT calc VALUES(1 , 2, '9/1/2005')

SELECT MAX(c2) as C2Val, c1 as GroupByCol
FROM calc calc1
WHERE c3 = (SELECT MAX(c3) FROM calc calc2 WHERE calc2.c1 = calc1.c1)
group by c1


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Robert Hamilton" <RobertHamilton (AT) discussions (DOT) microsoft.com> wrote

Thanks!

"Allan Mitchell" wrote:

Thanks!

"Alejandro Mesa" wrote: