求sql语句！！！！

[麻子Mozart]

现有A表如下：主键（A1,A3）
A1(int) A2(int) A3(int) A4(int)
10 3 3 6
10 5 6 3
10 8 9 1
12 2 2 5
12 6 3 6
15 1 2 7
想得到同一A1的最大A2的所有记录
结果：
10 8 9 1
12 6 3 6
15 1 2 7

[我姓区不姓区]

select * from A a1 where A2=(select max(A2) from A a2 where a1.A1=a2.A1)

[lyymax]

select table1.* from table1, (select A1, Max(A2) A2 from table1 group by A1) table2
	where table1.a1 = table2.a1 and table1.a2=table2.a2

[shangyuntao664056284]

select * from A where A2=(select max(A2) from A a2 where a1.A1=a2.A1)

[czbbbs]

select a1,a2,a3,a4
from
(
select RANK() over(PARTITION BY a1 Order by a2 desc) Sortno,a1,a2,a3,a4
from A
)
where Sortno =1

[chinesesdn]

关注

[冰岛男孩]

[Quote=引用 5 楼 chinesesdn 的回复:]
关注
[/Quote]
支持一楼的

[xuqunying0545]

up

[bhtfg538]

sql 2005

用 这个最快
或者
使用 Row_number()
select a1,a2,a3,a4
from
(
select RANK() over(PARTITION BY a1 Order by a2 desc) Sortno,a1,a2,a3,a4
from A
)
where Sortno =1

[PandaIT]

[Quote=引用 1 楼 ojlovecd 的回复:]
SQL code
select * from A a1 where A2=(select max(A2) from A a2 where a1.A1=a2.A1)
[/Quote]

此SQL语句刚刚滴

[PandaIT]

[Quote=引用 1 楼 ojlovecd 的回复:]
SQL code
select * from A a1 where A2=(select max(A2) from A a2 where a1.A1=a2.A1)
[/Quote]

此SQL语句刚刚滴

[橙子撸代码]

支持1楼
必须用select嵌套

[挨踢民工的乐章]

我也发过类似的帖子，看结果：
http://topic.csdn.net/u/20090102/22/b7694b23-d874-4525-b462-70f978959f22.html

sql很经典的问题。。