over partition簡單使用

今天在網上看到的over partition簡單使用
http://www.itpub.net/thread-1940278-1-1.html
create table T1
(
  id          VARCHAR2(10),
  appid       VARCHAR2(10),
  key         VARCHAR2(10),
  version     VARCHAR2(64),
  result      VARCHAR2(10),
  create_date DATE
);
insert into T1 (id, appid, key, version, result, create_date) values ('1', '1', 'ABC', '9CE5B7149CDB852BC3AE1F7191920E9E', '1', to_date('18-10-2015 11:10:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('2', '1', 'BCD', '9CE5B7149CDB852BC3AE1F7191920E9E', '1', to_date('18-10-2015 11:20:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('3', '1', 'CDE', '9CE5B7149CDB852BC3AE1F7191920E9E', '0', to_date('18-10-2015 11:30:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('4', '1', 'DEF', '9CE5B7149CDB852BC3AE1F7191920E9E', '1', to_date('18-10-2015 12:40:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('5', '1', 'EFG', '9CE5B7149CDB852BC3AE1F7191920E9E', '1', to_date('18-10-2015 12:50:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('6', '1', 'ABC', 'EE5A53962E9E26BBA230E253FB6A9B29', '1', to_date('17-10-2015 12:01:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('7', '1', 'BCD', 'EE5A53962E9E26BBA230E253FB6A9B29', '0', to_date('17-10-2015 12:20:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('8', '1', 'CDE', 'EE5A53962E9E26BBA230E253FB6A9B29', '1', to_date('17-10-2015 12:30:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('9', '1', 'DEF', 'EE5A53962E9E26BBA230E253FB6A9B29', '0', to_date('17-10-2015 12:40:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('10', '1', 'CDE', 'EE5A53962E9E26BBA230E253FB6A9B29', '1', to_date('17-10-2015 12:41:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('11', '1', 'ABC', '9CE5B7149CDB852BC3AE1F7191920E9E', '1', to_date('19-10-2015 11:10:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('12', '1', 'BCD', '9CE5B7149CDB852BC3AE1F7191920E9E', '1', to_date('19-10-2015 11:20:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('13', '1', 'CDE', '9CE5B7149CDB852BC3AE1F7191920E9E', '0', to_date('19-10-2015 11:30:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('14', '1', 'DEF', '9CE5B7149CDB852BC3AE1F7191920E9E', '1', to_date('19-10-2015 12:40:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('15', '1', 'EFG', '9CE5B7149CDB852BC3AE1F7191920E9E', '1', to_date('19-10-2015 12:50:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('16', '1', 'ABC', 'EE5A53962E9E26BBA230E253FB6A9B29', '1', to_date('20-10-2015 12:01:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('17', '1', 'BCD', 'EE5A53962E9E26BBA230E253FB6A9B29', '0', to_date('20-10-2015 12:20:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('18', '1', 'CDE', 'EE5A53962E9E26BBA230E253FB6A9B29', '1', to_date('20-10-2015 12:30:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('19', '1', 'DEF', 'EE5A53962E9E26BBA230E253FB6A9B29', '0', to_date('20-10-2015 12:40:02', 'dd-mm-yyyy hh24:mi:ss'));
insert into T1 (id, appid, key, version, result, create_date) values ('20', '1', 'CDE', 'EE5A53962E9E26BBA230E253FB6A9B29', '1', to_date('20-10-2015 12:41:02', 'dd-mm-yyyy hh24:mi:ss'));

select * from (select t1.*,row_number() over (partition by key order by create_date desc) rn from t1 where result=1) where rn=1  -->按照key匪類，時間倒序排列，取result=1的值。

ID APPID KEY VERSION                                                   RESULT CREATE_DATE           RN
1 16       1     ABC EE5A53962E9E26BBA230E253FB6A9B29 1          2015/10/20 12:01:02 1
2 12       1     BCD 9CE5B7149CDB852BC3AE1F7191920E9E 1          2015/10/19 11:20:02 1
3 20       1     CDE EE5A53962E9E26BBA230E253FB6A9B29 1          2015/10/20 12:41:02 1
4 14       1     DEF 9CE5B7149CDB852BC3AE1F7191920E9E 1          2015/10/19 12:40:02 1
5 15       1 EFG 9CE5B7    149CDB852BC3AE1F7191920E9E 1          2015/10/19 12:50:02 1