SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Oracle Database Tutorials and Examples
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.






Oracle PL/SQL Analytical Functions : First_Value

PL/SQL First_Value() function is an analytical function in Oracle database server. Oracle First_Value analytical function returns the first expression in the order defined by "Order By" clause. The expression returned as the value of the select field and the "order by" clause for sort process are part of the PL/SQL First_Value() function.

FIRST_VALUE(expression IGNORE NULLS) OVER (PARTITION BY expression ORDER BY expression)

As PL/SQL developers will realize from the above syntax they can ommit NULL values by using the Ignore Null hint within the First_Value() expression.





When I apply the First_Value() function over Oracle 10g Express edition database on Countries table as seen in the below sql query,

SELECT
 t.country_id,
 t.country_name,
 t.region_id,
 FIRST_VALUE(country_name) OVER(PARTITION BY region_id ORDER BY country_id) first_in_region
FROM hr.COUNTRIES t
ORDER BY region_id, country_name

The output of the above PL/SQL Select statement which is using First_Value() function is as follows:

PL/SQL First_Value() function in Oracle database

Although there are corresponding functions of Oracle analytical functions in SQL Server named as Windows functions, First_Value() and Last_Value() function did not have an equivalent function in Microsoft SQL Server database server until the release of SQL Server 2011 Denali CTP3 version. But of course there are workarounds for getting same results that PL/SQL First_Value function provides in Transact-SQL as well. For SQL Server versions starting with SQL Server 2011 CTP3, T-SQL includes new SQL Analytic functions like First_Value() and Last_Value().










Oracle and PL/SQL Tutorials

Oracle Database Articles









Related Oracle Database and PL/SQL Tutorials

Oracle VirtualBox Download for Windows 7
PL/SQL Double Quotes for Space in Table Name in Oracle Database
Oracle PL/SQL Analytical Functions : First_Value
"ORA-00923: FROM keyword not found where expected" Error
Download Oracle 11g Database Free
Oracle Tools and Charts - Download Oracle Data Dictionary Poster
Download Oracle Database 10g Express Edition for Free






Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems