求条SELECT语句，有关DISTINCT的，在线等

[jnyezhi]

有两个表
table1
id1 name
1 name1
2 name2


table2
id2 id1
1 1
2 1
3 2
4 1
5 2

两个表都还有其他字段，就不列出了

table1对table2是一对多的，我希望得到的结果是：
1、按table2.id2逆序排列
2、对于id1相同的结果，只显示id2最大的一条

也就是说我检索出的结果应当只包含两条，分别是
id1 id2 name
2 5 name2
1 4 name1

我用尽了办法，包括DISTINCT和GROUP BY，却都无法实现，用下面这句话能实现唯一，但顺序却不是降序，到底怎么回事呢？请高手指教！
我用的句子：SELECT table1.id1,table1.name,table2.id2 FROM table1,table2 WHERE table2.id1=table1.id1 GROUP BY table1.id1 DESC ORDER BY table1.id1 DESC
得到的结果：
id1 id2 name
2 3 name2
1 1 name1

[liuyann]

select *
from table2 a
where id2 = (select max(id2) from table2 where id1=a.id1) ;

== 思想重于技巧 ==

[liuyann]

select id1,id2,name
from table2 a inner join table1 b on a.id1=b.id1
where a.id2 = (select max(id2) from table2 where id1=a.id1) ;

== 思想重于技巧 ==

[liangwh2008]

要给分给我啊
SELECT table1.id1, max( table2.id2 ) AS id2
FROM table1
LEFT JOIN table2 ON table1.id1 = table2.id1
GROUP BY table1.id1 order by table1.id1 desc

[liangwh2008]

SELECT table1.id1, max( table2.id2 ) AS id2,table1.name
FROM table1
LEFT JOIN table2 ON table1.id1 = table2.id1
GROUP BY table1.id1 order by table1.id1 desc
补充table1.name

[懒得去死]

问过好多遍了。

http://blog.chinaunix.net/u/29134/showart_411484.html

[wwwwb]

select id1,max(id2) from tt group by id1 order by id1,2

[wwwwb]

select a.*,b.name from tt1 b inner join
(select id1,max(id2) as ma from tt group by id1) a
on a.id1=b.id1
order by id1 desc,ma desc