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;

     product_name       |  category   | stock_quantity
------------------------+-------------+----------------
Logitech MX Master 3    | Accessories |            234
Wireless Charger        | Accessories |            289
Power Bank 20000mAh     | Accessories |            234
USB-C Hub               | Accessories |            345
Laptop Stand            | Accessories |            143
Mechanical Keyboard RGB | Accessories |            167
Webcam HD 1080p         | Accessories |            198
Bluetooth Speaker       | Audio       |             76
Sony WH-1000XM4         | Audio       |             34
AirPods Pro             | Audio       |             89
Dash Cam                | Auto        |             83
Car Phone Mount         | Auto        |            456
GoPro Hero 11           | Camera      |             43
Canon EOS R5            | Camera      |              6
DJI Mini 3              | Drones      |             27
(15 rows)

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;

     product_name       |  category   | stock_quantity | min_stock_quantity | max_stock_quantity
------------------------+-------------+----------------+--------------------+--------------------
Laptop Stand            | Accessories |            143 |                143 |                345
Mechanical Keyboard RGB | Accessories |            167 |                143 |                345
Webcam HD 1080p         | Accessories |            198 |                143 |                345
Logitech MX Master 3    | Accessories |            234 |                143 |                345
Power Bank 20000mAh     | Accessories |            234 |                143 |                345
Wireless Charger        | Accessories |            289 |                143 |                345
USB-C Hub               | Accessories |            345 |                143 |                345
Sony WH-1000XM4         | Audio       |             34 |                 34 |                 89
Bluetooth Speaker       | Audio       |             76 |                 34 |                 89
AirPods Pro             | Audio       |             89 |                 34 |                 89
Dash Cam                | Auto        |             83 |                 83 |                456
Car Phone Mount         | Auto        |            456 |                 83 |                456
Canon EOS R5            | Camera      |              6 |                  6 |                 43
GoPro Hero 11           | Camera      |             43 |                  6 |                 43
DJI Mini 3              | Drones      |             27 |                 27 |                 27
(15 rows)

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;

 category     | min_stock_quantity | max_stock_quantity
--------------+--------------------+--------------------
Accessories   |                143 |                345
Audio         |                 34 |                 89
Auto          |                 83 |                456
Camera        |                  6 |                 43
Drones        |                 27 |                 27
Electronics   |                 41 |                 41
Gaming        |                  8 |                178
Health        |                 92 |                156
Home          |                 36 |                267
Kitchen       |                 54 |                198
Laptops       |                 12 |                 23
Monitors      |                 52 |                 52
Office        |                 23 |                 89
Personal Care |                 78 |                167
Smart Home    |                 47 |                125
(15 rows)