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:
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().