怎样得到name相同的最大的两个ID？

[arclala]

有以下的表，

Name ID
a 1
a 2
b 1
b 2
b 3
c 1
c 2
c 3
c 4
……
怎样得到这样的结果？
Name ID
a 1
a 2
b 2
b 3
c 3
c 4
即得到name相同的最大的两个ID

[outwindows]

select * from 表 aa
where ID in (select top 2 ID from 表 where Name=aa.Name order by ID desc)
order by aa.Name

[zjcxc]

select * from 表 a
where id in(
select top 2 id from 表 where name=a.name order by id desc)

[木一人]

----建立测试环境
create table pp (name varchar (5),id int)

insert into pp
select 'a', 1 union
select 'a', 2 union
select 'b', 1 union
select 'b', 2 union
select 'b', 3 union
select 'c', 1 union
select 'c', 2 union
select 'c', 3 union
select 'c', 4


----测试
select * from pp a where a.id in (select top 2 id from pp where name=a.name
order by id desc) order by name
----结果
name id
---- --
a 1
a 2
b 2
b 3
c 3
c 4

[outwindows]

--建表
create table CY_T(Name varchar(10),ID int)
go

insert into CY_T
select 'a', 1
union all select 'a', 2
union all select 'b', 1
union all select 'b', 2
union all select 'b', 3
union all select 'c', 1
union all select 'c', 2
union all select 'c', 3
union all select 'c', 4

--测试
select * from CY_T aa
where ID in (select top 2 ID from CY_T where Name=aa.Name order by ID desc)
order by aa.Name

--结果
/*
----------
Name ID
a 1
a 2
b 2
b 3
c 3
c 4
*/

--删除表
drop table CY_T

[bernice99]

select * from 表 a
where id in(
select top 2 * from 表 where name=a.name order by id desc)

可以吗？

[studyzhl]

select * from 表 a
where id in(
select top 2 * from 表 where name=a.name order by id desc)

当然可以啦！