October 30, 2009 at 8:33 pm
I have a table which has the following 5 columns :
name | date | AAA | BBB | CCC
A 10/10/2009
A 9/9/2009
B 10/10/2009
B 9/9/2009
B 8/8/2009
C 1/2/2008
D 3/5/2007
E 2/3/2009
F 1/10/2009
F 2/10/2009
F 3/10/2009
F 4/10/2009
date:mm/dd/yyyy
I need t-sql to display only those rows with the most recent date.
result should be
name | date | AAA | BBB | CCC
A 10/10/2009
B 10/10/2009
C 1/2/2008
D 3/5/2007
E 2/3/2009
F 4/10/2009
Thanks
October 30, 2009 at 9:41 pm
sql2000-915039 (10/30/2009)
I need t-sql to display only those rows with the most recent date.
select name,max(date),max(col1)...
from MYTABLE
GROUP BY name
October 30, 2009 at 10:02 pm
Actually, using SQL Server 2005, this may be the better choice as it keeps the data with the appropriate rows.
create table #MyTest (
[name] char(1),
[date] datetime,
[AAA] varchar(10) null,
[BBB] varchar(10) null,
[CCC] varchar(10) null
);
insert into #MyTest([name],[date], [AAA])
select 'A', '2009-10-10', '10' union all
select 'A', '2009-09-09', '9' union all
select 'B', '2009-10-10', '12' union all
select 'B', '2009-09-09', '11' union all
select 'B', '2009-08-08', '10' union all
select 'C', '2008-01-02', '1' union all
select 'D', '2007-03-05', '1' union all
select 'E', '2009-02-03', '1' union all
select 'F', '2009-01-10', '4' union all
select 'F', '2009-02-10', '3' union all
select 'F', '2009-03-10', '2' union all
select 'F', '2009-04-10', '1';
with MyCte (
[RowNumber],
[name],
[date],
[AAA],
[BBB],
[CCC]
) as (
select
row_number() over (partition by [name] order by [date] desc),
[name],
[date],
[AAA],
[BBB],
[CCC]
from
#MyTest
)
select
[name],
[date],
[AAA],
[BBB],
[CCC]
from
MyCte
where
[RowNumber] = 1;
drop table #MyTest;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply