请教一个sql（按某个字段分组然后从每组取出最大的一条纪录）？
请教一个sql（按某个字段分组然后从每组取出最大的一条纪录）？
比如表     data_table
字段有：id、name、address、tel、update
                  1       n1         a1               s1       20070801
                  2       n2         a1               s2       20070302
                  3       n3         a2               s3       20070501
                  4       n3         a2               s3       20070301
我想得到的数据是     按照   address   分组   然后取出每组中update最大的那几条纪录。结果应该是这两条纪录：
                  1       n1         a1               s1       20070801
                  3       n3         a2               s3       20070501

请问怎么写sql？谢谢

------解决方案--------------------
select id, name, address, tel, update
from (
select id, name, address, tel, update,
row_number() over(partition by address order by update desc) rn
from data_table
)
where rn = 1
------解决方案--------------------
SELECT * FROM A,(SELECT A.address,MAX(A.update) FROM A GROUP BY A.address) C
WHERE A.address = C.address
AND A.update = C.update
------解决方案--------------------
SQL> select *
2 from (
3 select tt.*,
4 row_number() over(partition by tt.address order by tt.update1 desc) rn
5 from (
6 select 1 as id, 'n1 ' as name, 'a1 ' as address, 's1 ' as tel, '20070801 ' as update1 from dual
7 union all
8 select 2 as id, 'n2 ' as name, 'a1 ' as address, 's2 ' as tel, '20070302 ' as update1 from dual
9 union all
10 select 3 as id, 'n3 ' as name, 'a2 ' as address, 's3 ' as tel, '20070501 ' as update1 from dual
11 union all
12 select 4 as id, 'n3 ' as name, 'a2 ' as address, 's3 ' as tel, '20070301 ' as update1 from dual
13 )tt
14 )zz
15 where zz.rn = 1;

ID NAME ADDRESS TEL UPDATE1 RN
---------- ---- ------- --- -------- ----------
1 n1 a1 s1 20070801 1
3 n3 a2 s3 20070501 1

------解决方案--------------------
select *
from data_table t
where not exists(select 1
from data_table
where address=t.address
and update> t.update)
------解决方案--------------------
回复人：kimi_deng()