更改一下
select id ,[type] ,date from(
select *,row_number()over(partition by type id order by id desc) as num)a
where num=1

------解决方案--------------------
SQL code

更改一下
select id ,[type] ,date from(  
select *,row_number()over(partition by type order by id desc) as num from @table
)a where num=1



------解决方案--------------------
SQL code

declare @table table(id int ,[type] int ,date datetime)

insert into @table 
select 1,1,'2009-11-11 00:00:00' union all 
select 2,1,'2010-11-11 00:00:00' union all 
select 3,1,'2011-11-11 00:00:00' union all 
select 4,2,'2009-11-11 00:00:00' union all 
select 5,2,'2011-11-11 00:00:00' 

select * from @table t
where id=(select max(id) from @table where [type]=t.[type])
order by 1

/*
id          type        date
----------- ----------- -----------------------
3           1           2011-11-11 00:00:00.000
5           2           2011-11-11 00:00:00.000
*/

------解决方案--------------------
SQL code
declare @table table(id int ,[type] int ,date datetime)

insert into @table  
select 1,1,'2009-11-11 00:00:00' union all  
select 2,1,'2010-11-11 00:00:00' union all  
select 3,1,'2011-11-11 00:00:00' union all  
select 4,2,'2009-11-11 00:00:00' union all  
select 5,2,'2011-11-11 00:00:00'  


select t.* from @table t where id = (select max(id) from @table where type = t.type ) order by t.type
/*
id          type        date                                                   
----------- ----------- ------------------------------------------------------ 
3           1           2011-11-11 00:00:00.000
5           2           2011-11-11 00:00:00.000

（所影响的行数为 2 行）
*/

select t.* from @table t where not exists (select 1 from @table where type = t.type and id > t.id) order by t.type
/*
id          type        date                                                   
----------- ----------- ------------------------------------------------------ 
3           1           2011-11-11 00:00:00.000
5           2           2011-11-11 00:00:00.000

（所影响的行数为 2 行）
*/

------解决方案--------------------
SQL code

declare @table table(id int ,[type] int ,date datetime)

insert into @table 
select 1,1,'2009-11-11 00:00:00' union all 
select 2,1,'2010-11-11 00:00:00' union all 
select 3,1,'2011-11-11 00:00:00' union all 
select 4,2,'2009-11-11 00:00:00' union all 
select 5,2,'2011-11-11 00:00:00' 

select id ,[type] ,date from( 
select *,row_number()over(partition by [type] order by id desc) as num from @table
)a where num=1

--
select * from @table a where id=(
select MAX(id) from @table b where a.[type]=b.[type])
order by id
/*
id    type    date
3    1    2011-11-11 00:00:00.000
5    2    2011-11-11 00:00:00.000
*/

------解决方案--------------------