FIRST_VALUE
Another useful window function is the FIRST_VALUE function. This returns returns the
first value of an ordered output.
The syntax of the first value window function looks much like any other function.
FIRST_VALUE( column ) OVER( PARTITION BY partition_column ORDER BY order_column )
where
column: the field you wish to retain the first value
partition_column: column for the partition expression
order_column: column for the ordering expression.
Let's use an example to implement this function with an example.
Example
We have a table with products that looks like below:
SELECT
product_name,
category,
stock_quantity
FROM products
WHERE stock_quantity IS NOT NULL
ORDER BY category ASC
limit 15;
Now suppose we are interested in fetching the a product per category that has the top stock_quantity. We could
use the first_value to achieve this. See the output below
SELECT
product_name,
category,
stock_quantity,
FIRST_VALUE(stock_quantity) OVER (PARTITION BY category ORDER BY stock_quantity ASC) AS min_stock_quantity,
FIRST_VALUE(stock_quantity) OVER (PARTITION BY category ORDER BY stock_quantity DESC) AS max_stock_quantity
FROM products
WHERE stock_quantity IS NOT NULL
LIMIT 15;
Notice that the min_stock_quantity and max_stock_quantity represent
the minimum and the maximum stock_quantity available at the category level of the products.
Of course to simply get only these values, we can remove the product name and apply a distinct value.
SELECT distinct category,
FIRST_VALUE(stock_quantity) OVER (PARTITION BY category ORDER BY stock_quantity ASC) AS min_stock_quantity,
FIRST_VALUE(stock_quantity) OVER (PARTITION BY category ORDER BY stock_quantity DESC) AS max_stock_quantity
FROM products
WHERE stock_quantity IS NOT NULL
ORDER BY category
LIMIT 15;