FIRST_VALUE is one of the vital Analytic functions of Oracle. It is used to get the first value in an ordered set of values from an analytic window. The FIRST_VALUE function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g and Oracle 9i.
Syntax 1:
FIRST_VALUE (expression) [RESPECT NULLS | IGNORE NULLS] OVER ([query_partition_clause] [order_by_clause [windowing_clause]])
Syntax 2:
FIRST_VALUE (expression [RESPECT NULLS | IGNORE NULLS]) OVER ([query_partition_clause] [order_by_clause [windowing_clause]])
Parameters:
expression:
It is used to specify the expressions or column whose first value needs to be retrieved.
RESPECT NULLS | IGNORE NULLS: It is an optional parameter which is used to specify whether to include or ignore the NULL values in the calculation. The default value is RESPECT NULLS.
query_partition_clause: It is also an optional parameter which is used to partition the results into groups.
order_by_clause: It is also an optional parameter which is used to order the data within each partition.
windowing_clause: It is also an optional parameter which is used to specify the rows in the analytic window to be evaluated.
Values of windowing_clause:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: It is the default value that changes the Last row in the window with a change in the current row.
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: It changes the First row in the windows with a change in the current row.
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: It includes all the rows in the window, regardless of the current row.
Example 1: Without using query_partition_clause.
Students Table:
ID MARKS EXTRA_MARKS 1 80 10 2 100 20 3 95 15 4 75 10 5 85 15
Query:
SELECT DISTINCT FIRST_VALUE (marks) OVER (ORDER BY marks DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "HIGHEST" FROM students; |
Output:
HIGHEST 100
Explanation:
Here, the FIRST_VALUE function is used to get the highest ‘marks’ from the ‘students’ table. The ORDER BY clause will allow the analytic window to sort the data in descending order. The windowing clause will then include all the rows in the window, regardless of the current row.
Example 2: Using the query_partition_clause.
Students Table:
ID MARKS EXTRA_MARKS 1 80 10 2 100 20 3 95 15 4 75 10 5 85 15
Query:
SELECT DISTINCT extra_marks, FIRST_VALUE (marks) OVER (PARTITION BY extra_marks ORDER BY marks DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "HIGHEST" FROM students WHERE extra_marks in (10, 15) ORDER BY extra_marks; |
Output:
EXTRA_MARKS HIGHEST 10 80 15 95
Explanation:
Here, the FIRST_VALUE function is used to get the highest ‘marks’ from the ‘students’ table. The ORDER BY clause will allow the analytic window to sort the data in descending order after the partition of the results by ‘extra_marks’. The windowing clause will then include all the rows in the window, regardless of the current row.