• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / function / FIRST Function in Oracle SQL – PLSQL

FIRST Function in Oracle SQL – PLSQL

November 21, 2012 by techhoneyadmin

The FIRST Function in Oracle SQL / PLSQL is used to get the FIRST value in an ordered set of records.
The FIRST function can be used with the following functions.

  1. MIN()
  2. MAX()
  3. COUNT()
  4. SUM()
  5. AVG()
  6. STDDEV() and
  7. VARIANCE()

Syntax for using the FIRST function in Oracle SQL / PLSQL is ;
SELECT
AGGREGATE_FUNCTION() KEEP (RANK_FUNCTION FIRST ORDER BY AGGREGATE_FUNCTION(column))
FROM table
GROUP BY column(s);

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales 10

Now, suppose we want to get the name of the department where the total salary being given is lesser than the total of salary in any other department, then we can achieve the same using FIRST function as:

SELECT MAX(department) KEEP (DENSE_RANK FIRST ORDER BY SUM(salary)) Min_Sal_Department
FROM employee
GROUP BY department;

We will get the following result:

MIN_SAL_DEPARTMENT
Support

Here we can see that we have successfully fetched the name of the department in which the total salary is lesser than total salary of any other department.


Filed Under: function Tagged With: FIRST function in oracle plsql, FIRST function in oracle sql, FIRSTPLSQL, how to use FIRST function in oracle database query, how to use FIRST function in oracle plsql, how to use FIRST function in oracle sql, syntax and example of FIRST function in oracle database query, syntax and example of FIRST function in oracle plsql, syntax and example of FIRST function in oracle sql, using FIRST function in oracle database query, using FIRST function in oracle plsql, using FIRST function in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in