id name address time
1 a dadsa 2008-10-10 12:12:12
2 a dasdas 2008-10-10 12:12:13
3 a asda 2008-10-10 12:12:14
4 b asd 2008-10-10 12:12:15
5 c asd 2008-10-10 12:12:16
6 e asd 2008-10-10 12:12:17
7 e asd 2008-10-10 12:12:18
8 d asd 2008-10-10 12:12:19
9 d asd 2008-10-10 12:12:10
查找 name相同情况下 时间最大的那条数据 sql语句是怎么写的??结果应该是 id=3 , id=4 , id=5 , id=7 ,id=8的这几条数据
谢谢
1 a dadsa 2008-10-10 12:12:12
2 a dasdas 2008-10-10 12:12:13
3 a asda 2008-10-10 12:12:14
4 b asd 2008-10-10 12:12:15
5 c asd 2008-10-10 12:12:16
6 e asd 2008-10-10 12:12:17
7 e asd 2008-10-10 12:12:18
8 d asd 2008-10-10 12:12:19
9 d asd 2008-10-10 12:12:10
查找 name相同情况下 时间最大的那条数据 sql语句是怎么写的??结果应该是 id=3 , id=4 , id=5 , id=7 ,id=8的这几条数据
谢谢
from (select t.*,
row_number() over(partition by name order by time desc) rn
from test t)
where rn = 1
(select name,max(time) time from table group by name ) tt
where tt.name =name and tt.time =time
SQL> SELECT *
2 FROM TABLE_NAME T1
3 WHERE NOT EXISTS (SELECT 1
4 FROM TABLE_NAME T2
5 WHERE T1.NAME = T2.NAME
6 AND T1.TIME < T2.TIME); ID NAME ADDRESS TIME
---------- ---- ------- -----------
3 A ASDA 2008-10-10 12:12:14
4 B ASD 2008-10-10 12:12:15
5 C ASD 2008-10-10 12:12:16
7 E ASD 2008-10-10 12:12:18
8 D ASD 2008-10-10 12:12:19
select id, name, address, time
from (select t.*,
rank() over(partition by name order by time desc) rn
from test t)
where rn = 1