请教一个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()
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
这里存在一点问题，select选择的应该只有A（data_table）表中4个字段，子查询的表C中只是包括2个字段，如果用*就会增加查询的字段数，不过我觉得他的思路很好！做法很简单
[解决办法]
kimi_deng() 的做法，的确不严谨。
只是没有考虑到，按照address分组以后，假如update有重复的值，怎么再区分呢？
这个就需要楼主把表的结构，解释一番。
