Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL FIRST Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 169 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?