請教一條查詢語句

[wenjinhe]

我現在需要按照Pro字段查詢符合要求的語句
具體字段如下：
PROJECT PRO NAME OLD
E0056 P001 WE ..
E0056 P002 WR ..
E0032 P001 W5 ..
ER035 P001 WT ..
ER035 P002 TR ..
ER039 P001 WT ..
ER039 P002 TR ..
ER039 P003 YT ..
ER039 P004 ER ..
..........
具體要求是按照PRO字段的編號，只保留編號最大的一個，比如查詢后只有：
PROJECT PRO NAME OLD
E0056 P002 WR ..
E0032 P001 W5 ..
ER035 P002 TR ..
ER039 P004 ER ..
..........
請教高手該如何寫查詢語句~~？

[itblog]

select * from 表 a where not exists(select 1 from 表 where project=a.project and pro>a.pro)

[paoluo]

Select A.*
From TableName A
Inner Join (Select PROJECT,Max(PRO) As PRO From TableName Group By PROJECT) B
On A.PROJECT=B.PROJECT And A.PRO=B.PRO

[hjw01592]

select a.project,a.pro,a.name,a.old from tablename as a,
(select project,max(cast(right(pro,3) as int) as pro from tablename group by project) as b
where a.project=b.project and left(a.pro,3)=b.pro

[hjw01592]

不好意思，这里“where a.project=b.project and left(a.pro,3)=b.pro”应该是where a.project=b.project and right(a.pro,3)=b.pro

[xiaoxiangqing]

select * from YourTable a where PRO in(select max(PRO) from YourTable where
a.PROJECT=PROJECT)