Unexpected results when using FIRST_VALUE() in SQL Server 2012
The provided examples show very clearly that there is an inconsistency in the implementation of the FIRST_VALUE()
analytic function.
Depending on whether the underlying table in the FROM
clause is a base table (or temporary or a table variable or even a derived table created on the fly) in one case and a derived table (or cte) created by the LEFT JOIN
of two created on the fly tables in the second case, the results are different. Seems like the NULL
values are ignored in the 2nd case or treated as high values.
And they shouldn't be different, because the result of a SQL query should not depend on how the FROM
clause gets the values of the table it provides to the SELECT
clause and also because documentation of the OVER
clause clearly states how NULL
values should be treated:
order_by_expression
Specifies a column or expression on which to sort. order_by_expression can only refer to columns made available by the FROM clause. An integer cannot be specified to represent a column name or alias.
...
ASC | DESC
Specifies that the values in the specified column should be sorted in ascending or descending order. ASC is the default sort order. Null values are treated as the lowest possible values.
So, the correct results - according to the SQL-Server documentation - are the ones that do not ignore NULL values. Any other result should not happen and since it does happen, it is a bug.
I suggested you test in the most recent version (and not only in the RTM) as it may have been identified and corrected in some service pack or update and if it's still there (or if you don't have a newrer version available) to submit this as a bug in the Connect site.
Update
For future reference, the bug was submitted by the OP. The link is: Connect item and (our) @Aaron Bertrand has commented there that it also appears in most current SQL 2014 builds.
A bit of a late answer to this post, but one to share nevertheless.
You can use the order by flag to "demote" the null values.
So in your case ... you can use
...FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY (iif(aval is null, 1,0)), aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv...
(Note that I use the value 1 for null values as it should sort the field ascending, so the non-null values will take precedence)
Cheers - LA.