这样的分组如何写?(50分)

有个表,字段为: &nbsp;material_no,bill_no,c_date <br><br>其中material_no ,bill_no,c_date 字段均有多条相同的记录<br><br>&nbsp;想找出 字段中c_date 中最大的整条记录如何写sql语句?

有个表,字段为: &nbsp;material_no,bill_no,c_date <br><br>&nbsp;material_no,bill_no,c_date <br>&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; 20070101<br>&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 20070902 <br>&nbsp; 1 &nbsp; &nbsp; 1 20070201<br>&nbsp; 2 &nbsp; &nbsp; 1 20070103<br>&nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 20070105 <br><br>想得到:<br>&nbsp; &nbsp;<br>&nbsp;material_no,bill_no,c_date &nbsp;<br>&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 20070902 &nbsp;<br>&nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 20070105 <br>sql语句如何写?<br>sql语句如何写?

select a.* from Table1 a, <br>(select material_no, max(c_date) as MaxDate from Table1 group by material_no) b<br>where a.material_no=b.material_no and a.c_date=b.MaxDate

select top 2 * from tabel1 order by c_Date desc

楼主的问题还有交待不清楚的地方：1、是不是相同的记录只显示一条？2、“想找出 字段中c_date 中最大的整条记录如何写sql语句?”这一句与你想得到的结果：“ material_no,bill_no,c_date &nbsp;<br>&nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp;20070902 &nbsp;<br>&nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp;20070105 &nbsp;<br>”矛盾。<br>不过看你的意思应该也是一个自连接问题，用Corn3的思路应该可以解决。

在Corn3的上改了一下:<br>SELECT DISTINCT a.*<br>FROM TABLE1 a INNER JOIN<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (SELECT material_no, MAX(c_date) AS MaxDate<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM Table1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GROUP BY material_no) b ON a.material_no = b.material_no AND <br>&nbsp; &nbsp; &nbsp; a.c_date = b.MaxDate<br>ORDER BY a.material_no