一个SQL搜索有关问题

一个SQL搜索问题
假如有表
id id2 name flag
1 1 a 0
2 1 b 1
3 1 c 0
4 2 d 0
5 2 e 1
6 3 g 1
7 4 ggg 0
8 4 kkk 1
9 4 ddd 0
10 4 iij 0

怎么取id2最后一个记录且 flag必须等于1
结果应该是：
id id2 name
2 1 b
5 2 e
6 3 g
8 4 kkk

[解决办法]

SQL code

-->TravyLee生成测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([id] int,[id2] int,[name] varchar(3),[flag] int)insert [test]select 1,1,'a',0 union allselect 2,1,'b',1 union allselect 3,1,'c',0 union allselect 4,2,'d',0 union allselect 5,2,'e',1 union allselect 6,3,'g',1 union allselect 7,4,'ggg',0 union allselect 8,4,'kkk',1 union allselect 9,4,'ddd',0 union allselect 10,4,'iij',0select [id],[id2],[name] from(select px=ROW_NUMBER()over(partition by [id2] order by [id]),* from test where [flag]=1)t where px=1/*id    id2    name2    1    b5    2    e6    3    g8    4    kkk*/
[解决办法]
SQL code
-->TravyLee生成测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([id] int,[id2] int,[name] varchar(3),[flag] int)insert [test]select 1,1,'a',0 union allselect 2,1,'b',1 union allselect 3,1,'c',0 union allselect 4,2,'d',0 union allselect 5,2,'e',1 union allselect 6,3,'g',1 union allselect 7,4,'ggg',0 union allselect 8,4,'kkk',1 union allselect 9,4,'ddd',0 union allselect 10,4,'iij',0--SQL Server 2000select id,id2,name from test awhere not exists(select 1 from test b where a.id2=b.id2 and a.id<b.id and b.flag=1)and a.flag=1/*id    id2    name2    1    b5    2    e6    3    g8    4    kkk*/