FIRST_VALUE function and LAST_VALUE function examples

The FIRST_VALUE and LAST_VALUE functions return values from the first and last rows of a window. This allows a query to access
values from multiple rows at once, without the need for a self-join.

These two functions are different from the other window aggregate functions because they must be used with a window. Also,
unlike the other window aggregate functions, these functions allow the IGNORE NULLS clause. If IGNORE NULLS is specified,
the first or last non-NULL value of the desired expression is returned. Otherwise, the first or last value is returned.

The FIRST_VALUE function can be used to retrieve the first entry in an ordered group of values. The following query returns,
for each order, the product identifier of the order's first item; that is, the ProductID of the item with the smallest LineID
for each order.

Notice that the query uses the DISTINCT keyword to remove duplicates; without it, duplicate rows are returned for each item
in each order.

SELECT DISTINCT ID,
FIRST_VALUE( ProductID ) OVER ( PARTITION BY ID ORDER BY LineID )
FROM SalesOrderItems
ORDER BY ID;

A common use of the FIRST_VALUE function is to compare a value in each row with the maximum or minimum value within the current
group. The following query computes the total sales for each sales representative, and then compares that representative's
total sales with the maximum total sales for the same product. The result is expressed as a percentage of the maximum total
sales.

The FIRST_VALUE and LAST_VALUE functions are useful when you have made your data more dense and you need to populate values
instead of having NULLs. For example, suppose the sales representative with the highest total sales each day wins the distinction
of Representative of the Day. The following query lists the winning sales representatives for the first week of April, 2001:

However, no results are returned for days in which no sales were made. The following query makes the data more dense, creating
records for days in which no sales were made. Additionally, it uses the LAST_VALUE function to populate the NULL values for
rep_of_the_day (on non-winning days) with the ID of the last winning representative, until a new winner occurs in the results.

The derived table v from the previous query is joined to a derived table d, which contains all the dates under consideration.
This yields a row for each desired day, but this outer join contains NULL in the SalesRepresentative column for dates on which
no sales were made. Using the LAST_VALUE function solves this problem by defining rep_of_the_day for a given row to be the
last non-NULL value of SalesRepresentative leading up to the corresponding day.