Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

oracle keep(first/last)


 ÏÈ¿´Ò»¶ÎORACLE¹Ù·½Îĵµ
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/analysis.htm#25806:
FIRST/LAST Functions
The FIRST/LAST aggregate functions allow you to return the result of an aggregate applied over a set of rows that rank as the first or last with respect to a given order specification. FIRST/LAST lets you order on column A but return an result of an aggregate applied on column B. This is valuable because it avoids the need for a self-join or subquery, thus improving performance. These functions begin with a tiebreaker function, which is a regular aggregate function (MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV) that produces the return value. The tiebreaker function is performed on the set rows (1 or more rows) that rank as first or last respect to the order specification to return a single value.
To specify the ordering used within each group, the FIRST/LAST functions add a new clause starting with the word KEEP.
´óÒâÊÇ˵FIRST/LASTº¯Êý°´ÕÕij¸ö×Ö¶ÎÅÅÐòºóÈ¡µÃµÚÒ»ÐлòÕß×îºóÒ»ÐÐ,FIRST/LAST¾Û¼¯º¯Êý¿ÉÒÔ°´AÁÐÅÅÐò,BÁоۼ¯,±ÜÃâÁË×ÔÁ¬½ÓºÍ×Ó²éѯ.·Ö×é¾ÛºÏº¯Êý(min,max....)λÓÚFIRST/LASTº¯Êý֮ǰ²úÉú¶àÐнá¹û¼¯,²¢ÇÒ°´ÕÕÅÅÐò·µ»ØFIRST/LASTµ¥¸öÖµ.
ÒªÖ¸¶¨ÔÚÿ¸ö×éµÄ˳Ðò£¬FIRST/LASTº¯Êý֮ǰ¼ÓÉÏÒԹؼü×ÖKEEP¿ªÊ¼¼´¿É
FIRST/LAST Syntax
These functions have the following syntax:
aggregate_function KEEP
( DENSE_RANK LAST ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
 [, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)
[OVER query_partitioning_clause]
Note that the ORDER BY clause can take multiple expressions.Çë×¢ÒâÔÚORDER BY×Ó¾ä¿ÉÒÔ²ÉÈ¡¶àÖÖ±íÏÖÐÎʽ
Returns the row ranked first using DENSE_RANK   
2ÖÖȡֵ£º
DENSE_RANK FIRST
DENSE_RANK LAST
ÔÚkeep (DENSE_RANK first ORDER BY sl) ½á¹û¼¯ÖÐÔÙÈ¡max¡¢minµÄÀý×Ó¡£
Àý×ÓÈçÏÂ:oracle·ÖÎöº¯ÊýÖУ¬keep and overµÄÇø±ð
¹«Ë¾²¿ÃÅÖÐÈ볧ʱ¼ä×îÔçµÄÔ±¹¤µÄнˮ×îСµÄÊǶàÉÙ
SQL>SELECT deptno,ename,empno,sal,
MIN(sal) KEEP (dense_rank FIRST ORDER BY hiredate) over (PARTITION


Ïà¹ØÎĵµ£º

Aix5.3ÏÂOracle 10gÉèÖù鵵ģʽµ½Îļþϵͳ


н¨Îļþϵͳ
        crfs  -v jfs  -g  rootvg  -m /arch -a \ size=32768 -a frag=512 -a nbpi=8192  Ò²¿ÉÒÔ smit¡£
¿ªÆô¹éµµÄ£Ê½
     $ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 10 13:38 ......

Upgrade Oracle 9i RAC to Oracle 10g RAC

Upgrade Oracle 9i RAC to Oracle 10g RAC
by Vincent Chan
This article provides the procedures for converting Oracle 9i (9.2.0.4) RAC to Oracle 10g (10.2.0.1) RAC on Red Hat Enterprise Linux 3 (RHEL3).
Oracle Database 10g and Oracle Real Application Cluster (RAC) 10g itself, boast many new and ex ......

Oracle RAC 10.2.0.1 Éý¼¶ 10.2.0.4 ¼òµ¥ÃèÊö

ÍÆ¼öcrs°æ±¾¸ßÓÚdb°æ±¾
If you also plan to apply this patch to Oracle Clusterware,
ensure that you apply the patch to Oracle Clusterware before
applying it to Oracle Database. This is because Oracle Clusterware
hould always be at the same or later version as the database.
Éæ¼°µ½Êý¾Ý¿â¶ø²»ÊÇʵÀýµ ......

Oracle Linux Æð動¤ÈÍ£Ö¹¤Î×Ô動»¯

¤½¤ÎËû¤Î¥ª¥Ú¥ìー¥Æ¥£¥ó¥°?¥·¥¹¥Æ¥à¤Ë¤ª¤±¤ë¥Çー¥¿¥Ùー¥¹¤ÎÆð動¤ÈÍ£Ö¹¤Î×Ô動»¯
dbstart¤ª¤è¤Ódbshut¥¹¥¯¥ê¥×¥È¤òʹÓ䷤ƥÇー¥¿¥Ùー¥¹¤ÎÆð動¤ÈÍ£Ö¹¤ò×Ô動»¯¤¹¤ë¤Ë¤Ï¡¢´Î¤ÎÊÖ順¤ò実ÐФ·¤Þ¤¹¡£
root¥æー¥¶ー¤Ç¥í¥°¥¤¥ó¤·¤Þ¤¹¡£
¥×¥é¥Ã¥È¥Õ ......

oracle 10g ¿ØÖÆÌ¨Æô¶¯¶Ë¿ÚÐÞ¸Ä

ÀýÈçÎÒÔÚoracle10gÀïÅäÖÃÁË2¸öÊý¾Ý¿âxljcºÍxldb,ÆÚ¿ØÖÆÌ«ä¯ÀÀÆ÷ÖÖÆô¶¯·Ö±ðΪ:xljcÔÚä¯ÀÀÆ÷ÖÐÊäÈë http://zhang:5500/em xldbÔÚä¯ÀÀÆ÷ÖÐÊäÈë http://zhang:1158/emÈç¹ûÍü¼ÇÁËÆô¶¯¶Ë¿ÚºÅ,¿ÉÒÔ²éѯÎļþ
ÀýÈçÎÒÔÚoracle10gÀïÅäÖÃÁË2¸öÊý¾Ý¿âxljcºÍxldb,ÆÚ¿ØÖÆÌ«ä¯ÀÀÆ÷ÖÖÆô¶¯·Ö±ðΪ:xljcÔÚä¯ÀÀÆ÷ÖÐÊäÈë http://zhang:5500/em
x ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ