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