分组排名

分组排名：

按object_type分组，求出前5个大的object_id的数据。

测试步骤：

SQL> create table test as select object_id, object_type from all_objects;

Table created.

SQL> SELECT *
  2    FROM (SELECT OBJECT_TYPE, OBJECT_ID,
  3                 ROW_NUMBER() OVER(PARTI
  4            FROM TEST
  5  ) WHERE RN<=5
  6  ORDER BY OBJECT_TYPE, OBJECT_ID, RN;

OBJECT_TYPE          OBJECT_ID         RN
------------------- ---------- ----------
CONSUMER GROUP            4867          3
CONSUMER GROUP            4869          2
CONSUMER GROUP           13748          1
DIRECTORY                10989          1
EVALUATION CONTEXT        8422          1
FUNCTION                  8626          5
FUNCTION                  8627          4
FUNCTION                  8639          3
FUNCTION                  8650          2
FUNCTION                 12505          1
INDEX                    13758          5

OBJECT_TYPE          OBJECT_ID         RN
------------------- ---------- ----------
INDEX                    13759          4
INDEX                    13761          3
INDEX                    13768          2
INDEX                    13874          1
INDEX PARTITION          13366          5
INDEX PARTITION          13368          4
INDEX PARTITION          13369          3
INDEX PARTITION          13370          2
INDEX PARTITION          13371          1
JOB                      12653          1
JOB CLASS                 5347          1

OBJECT_TYPE          OBJECT_ID         RN
------------------- ---------- ----------
LIBRARY                   7718          3
LIBRARY                   7719          2
LIBRARY                   7720          1
LOB                      13870          1
MATERIALIZED VIEW        13243          3
MATERIALIZED VIEW        13756          2
MATERIALIZED VIEW        13762          1
OPERATOR                  5954          5
OPERATOR                  7635          4
OPERATOR                  7637          3
OPERATOR                  7639          2

OBJECT_TYPE          OBJECT_ID         RN
------------------- ---------- ----------
OPERATOR                  7641          1
PACKAGE                   9782          5
PACKAGE                   9784          4
PACKAGE                  12624          3
PACKAGE                  12741          2
PACKAGE                  12743          1
PACKAGE BODY              6302          5
PACKAGE BODY              6303          4
PACKAGE BODY              6304          3
PACKAGE BODY              6305          2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27143/viewspace-681907/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27143/viewspace-681907/