一则over(partition by )的用法

   在别的论坛上看到有人发表如下的问题：

    

group by 子名问题
在一个表中能过group by 子名进行分组,怎样能得到第一组的第一条记录?
比如:有一个表scrap_t
documentno update_time bu name
1001 20061213 xx alex
1001 20060910 yy xxx
1002 20070910 zz xx
1002 20081213 jj dd
1002 -------------------
------------
我想先能过group by对documentno分组,然后取出每一组update_time最新的一行数据,其实可以能过以下语句实现:
select * from scrap_t
where update_time in (select max(update_time) from scrap_t group by documentno)
如果update_time都没有同的这个查询就是准确了,但总觉得这种查询得到的数据不是很准确,有没有更好的办法呢?请各位指教!

我们来做个实验：

[oracle@hundsun ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 24 21:48:58 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

idle> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 71305460 bytes
Database Buffers 92274688 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
idle> conn scott/tiger
Connected.
scott@NIUGE> create table t(documentno int,update_time number(8,0),bu varchar2(10), name varchar(20));

Table created.

scott@NIUGE> insert into t values(1001,'20061213','xx','alex');

1 row created.

scott@NIUGE> commit;

Commit complete.

scott@NIUGE> insert into t values(1001,'20060910','yy','xx');

1 row created.

insert into t values(1002,'20070910','zz','xx');
scott@NIUGE>
1 row created.

scott@NIUGE> insert into t values(1002,'20081213','jj','dd');

1 row created.

scott@NIUGE> commit;

Commit complete.

scott@NIUGE> select * from t
  2 /

DOCUMENTNO UPDATE_TIME BU NAME
---------- ----------- ---------- --------------------

      1001 20061213 xx alex
      1001 20060910 yy xx
      1002 20070910 zz xx
      1002 20081213 jj dd


 

我用over（partition by……）来实现：

 

 

scott@NIUGE> select * from t;

DOCUMENTNO UPDATE_TIME BU NAME
---------- ----------- ---------- --------------------

      1001 20061213 xx alex
      1001 20060910 yy xx
      1002 20070910 zz xx
      1002 20081213 jj dd

scott@NIUGE>
scott@NIUGE> select * from (select rank() over(partition by documentno order by update_time desc) r,a.* from t a) where r=1;

         R DOCUMENTNO UPDATE_TIME BU NAME
---------- ---------- ----------- ---------- --------------------

         1 1001 20061213 xx alex
         1 1002 20081213 jj dd

 

有兴趣的可以自己录入一些数据自己做做实验。