Quick Search:
 
 The Oracle PL/SQL FIRST Function      [Return To Index] Jump to:  

Term: FIRST

Definition:
The Oracle PL/SQL FIRST function can be used for both aggregate and analytic action on the group of logically sorted rows. It operates on values of these rows that are eligible to be ranked "first" or "last". The rows can be grouped using any of the analytic functions like MIN, MAX, SUM, AVG, COUNT, VARIANCE or STDDEV.

Example Syntax:

[AGGREGATE FUNCTION] KEEP (DENSE_RANK FIRST ORDER BY [COLUMN | EXPRESSION] [ASC | DESC] )
OVER (PARTITION BY [COLUMN] )


In the syntax, the KEEP keyword is used as an indication that only FIRST values are returned. Note that DENSE_RANK is always used to rank the grouped rows and extract the minimum or maximum value.

Notes:
  1. FIRST and LAST are the only functions that deviate from the general syntax of analytic functions.
  2. FIRST and LAST do not have the ORDER_BY inside the OVER clause.
  3. FIRST and LAST do not support any <window> clause.

Example Usage:

The SQL query below displays the salary of each employee in a department. At the same time it also shows the lowest salary in their department.

SELECT DEPTNO, ENAME ,SAL
MIN(sal) KEEP (DENSE_RANK FIRST ORDER_BY sal) OVER (PARTITION BY deptno) "Lowest"
FROM EMP
ORDER BY deptno, sal;



Related Links:

Related Code Snippets:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org