last-value.md 8.8 KB
title: "Last value in group" description: "Use idiomatic Materialize SQL to find the last value in each group." menu: main:
parent: idiomatic-materialize-sql
identifier: idiomatic-materialize-last-value
weight: 30
Overview
The "last value in each group" query pattern returns the last value, according to some ordering, in each group.
{{< callout >}}
Materialize and window functions
{{< idiomatic-sql/materialize-window-functions >}}
{{</ callout >}}
Idiomatic Materialize SQL
Idiomatic Materialize SQL: To find the last value in each group, use the MIN() or MAX() aggregate function in a subquery.
| Idiomatic Materialize SQL |
Use a subquery that uses the [MIN()](/sql/functions/#min) or
[MAX()](/sql/functions/#max) aggregate function.
```mzsql
SELECT tableA.fieldA, tableA.fieldB, minmax.Z
FROM tableA,
(SELECT fieldA,
MAX(fieldZ),
MIN(fieldZ)
FROM tableA
GROUP BY fieldA) minmax
WHERE tableA.fieldA = minmax.fieldA
ORDER BY fieldA ... ;
```
|
| Anti-pattern ❌ |
Do not use [`LAST_VALUE() OVER (PARTITION BY ... ORDER BY ... RANGE
...)` window function](/sql/functions/#last_value) for last value in each group
queries.
{{< note >}}
Materialize does not support `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING`.
{{}}
```nofmt
-- Unsupported --
SELECT fieldA, fieldB,
LAST_VALUE(fieldZ)
OVER (PARTITION BY fieldA ORDER BY fieldZ
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING),
LAST_VALUE(fieldZ)
OVER (PARTITION BY fieldA ORDER BY fieldZ DESC
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)
FROM tableA
ORDER BY fieldA, ...;
```
|
| Idiomatic Materialize SQL ✅ | ```mzsql SELECT o.order_id, minmax.highest_price, o.item, o.price, o.price - minmax.highest_price AS diff_highest_price FROM orders_view o, (SELECT order_id, MAX(price) AS highest_price FROM orders_view GROUP BY order_id) minmax WHERE o.order_id = minmax.order_id ORDER BY o.order_id, o.item; ``` |
| Anti-pattern ❌ |
Do not use of `LAST_VALUE() OVER (PARTITION BY ... ORDER BY ... RANGE ...)`
for last value in each group queries.
{{< note >}}
Materialize does not support `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING`.
{{}}
```nofmt
-- Unsupported --
SELECT order_id,
LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS highest_price,
item,
price,
price - LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS diff_highest_price
FROM orders_view
ORDER BY order_id, item;
```
|
Use MIN() to find the last values
Using idiomatic Materialize SQL, the following example finds the lowest item
price in each order and calculates the difference between the price of each item
in the order and the lowest price. That is, use a subquery that groups by the
order_id and selects MIN(price) as the lowest price
(i.e., last price if ordered by descending price value)
| Idiomatic Materialize SQL ✅ | ```mzsql SELECT o.order_id, minmax.lowest_price, o.item, o.price, o.price - minmax.lowest_price AS diff_lowest_price FROM orders_view o, (SELECT order_id, MIN(price) AS lowest_price FROM orders_view GROUP BY order_id) minmax WHERE o.order_id = minmax.order_id ORDER BY o.order_id, o.item; ``` |
| Anti-pattern ❌ |
Do not use `LAST_VALUE() OVER (PARTITION BY ... ORDER BY ... RANGE ... )`
for last value in each group queries.
{{< note >}}
Materialize does not support `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING`.
{{}}
```nofmt
-- Unsupported --
SELECT order_id,
LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price DESC
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS lowest_price,
item,
price,
price - LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price DESC
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS diff_lowest_price
FROM orders_view
ORDER BY order_id, item;
```
|
Use MIN() and MAX() to find the last values
Using idiomatic Materialize SQL, the following example finds the lowest and
highest item price in each order and calculate the difference for each item in
the order from these prices. That is, use a subquery that groups by the
order_id and selects MIN(price) as the lowest price
(i.e., last value if ordered by descending price values) and
MAX(price) as the highest price (i.e., last value if
ordered by ascending price values).
| Idiomatic Materialize SQL ✅ | ```mzsql SELECT o.order_id, minmax.lowest_price, minmax.highest_price, o.item, o.price, o.price - minmax.lowest_price AS diff_lowest_price, o.price - minmax.highest_price AS diff_highest_price FROM orders_view o, (SELECT order_id, MIN(price) AS lowest_price, MAX(price) AS highest_price FROM orders_view GROUP BY order_id) minmax WHERE o.order_id = minmax.order_id ORDER BY o.order_id, o.item; ``` | Anti-pattern ❌ |
Do not use `LAST_VALUE() OVER (PARTITION BY ... ORDER BY
)` for last value within groups queries.
{{< note >}}
Materialize does not support `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING`.
{{}}
```nofmt
-- Unsupported --
SELECT order_id,
LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price DESC
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS lowest_price,
LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS highest_price,
item,
price,
price - LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price DESC
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS diff_lowest_price,
price - LAST_VALUE(price)
OVER (PARTITION BY order_id ORDER BY price
RANGE BETWEEN
UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) AS diff_highest_price
FROM orders_view
ORDER BY order_id, item;
```
|