sql好象无法实现的先排序后分组，大手进来看看希望能得到解决

[llainn]

有这样一个数据库表
t1 t2 t3……n
--------------------------
aaa 100 剩余字段
bbb 80 剩余字段
aaa 90 剩余字段
ccc 70 剩余字段
ccc 500 剩余字段
ccc 20 剩余字段
bbb 30 剩余字段
bbb 40 剩余字段
bbb 50 剩余字段

我希望的最后结果就是：
t1 t2 t3……n
------------------
ccc 500 剩余字段
aaa 100 剩余字段
bbb 80 剩余字段

也就是以t1分组，取每组中t2最大的记录

帮我看看被怎么写
搞不定了
先谢谢了

[flyzq]

select a.* from table a,(select t1,max(t2) as t2 from table group by t1) b where a.t1=b.t1 and a.t2=b.t2

[kbxj406]

SELECT table.*
FROM table T1 INNER JOIN
(SELECT t1, MAX(t2) AS t2
FROM T1
GROUP BY t1) T2 ON T1.t1 = T2.t1 AND T1.t2 = T2.t2

试过了，可以的

[winner2050]

都对

[llainn]

两位大哥，你们太猛了

我看不懂，哪位好心人，留下QQ，一起讨论，我郁闷死了

[llainn]

我就一个表，看两位写的SQL语句，咋么整出两表名来呢？

[llainn]

二楼的做法
SELECT a.*
FROM (select * from admin inner join admin_list on admin.id = admin_list.username_id) as a
INNER JOIN
(SELECT a.admin.id, MAX(a.admin_list.id) AS admin_list_id
FROM (select * from admin inner join admin_list on admin.id = admin_list.username_id) as a
GROUP BY admin.id) as b on a.admin.id = b.admin.id and a.admin_list.id = b.admin_list_id

=============================================
一楼的做法
select a.*
from (select * from admin inner join admin_list on admin.id = admin_list.username_id) as a,
(select a.admin.id,max(a.admin_list.id) as admin_list_id
FROM (select * from admin inner join admin_list on admin.id = admin_list.username_id) as a
group by a.admin.id) as b where a.admin.id = b.admin.id and a.admin_list.id = b.admin_list_id

=======================
都可以成功

感谢paoluo(一天到晚游泳的鱼) 的详解

最后问一句：

inner jion
和
select * from T1,T2

有什么不同？

[llainn]

贴子回复次数大于跟给分次数

这是什么意思？

[llainn]

救命啊

[paoluo]

llainn(知易行难—www.dotnetcn.net) ( ) 信誉：96 Blog 2006-9-15 18:30:23 得分: 0



我就一个表，看两位写的SQL语句，咋么整出两表名来呢？



------------------------
table T1

(SELECT t1, MAX(t2) AS t2
FROM T1
GROUP BY t1) T2


T1是本身的表的別名，
T2是(SELECT t1, MAX(t2) AS t2
FROM T1
GROUP BY t1)
這個子查詢返回的結果集的別名