# Query with rolling values from last record

**URL:** <https://forum.confluent.io/t/query-with-rolling-values-from-last-record/4361>\
**Category:** ksqlDB\
**Created:** [4 March 2022 21:24 UTC](https://forum.confluent.io/t/query-with-rolling-values-from-last-record/4361 "2022-03-04T21:24:56Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![almindor](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/almindor/32/1597_2.png) [@almindor](https://forum.confluent.io/u/almindor)\
**Post date:** [4 March 2022 21:24 UTC](https://forum.confluent.io/t/query-with-rolling-values-from-last-record/4361/1 "2022-03-04T21:24:56Z")

</div>

I’m trying to make this kind of thing work in ksqlDB query:

```nohighlight
address, balance, change_id
a1,1,first -> a1,1,first
null,null,second -> a1,1,second
a1,2,third -> a1,2,third
null,null,fourth -> a1,2,fourth

```

In “descriptive SQL” terms:

```sql
SELECT
  topic.address,
  topic.balance,
  (SELECT LATEST(topic.change_id) FROM topic WHERE topic.address IS NULL) AS change_id
FROM topic
WITHIN 1 MINUTE
WHERE topic.address IS NOT NULL

```

Is something like this possible? Note that there’s no group key, as the latest entry is just that, the latest entry not tied to specific addresses here.

---

<div class="post-metadata">

**Author:** ![rick](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/rick/32/49_2.png) [@rick](https://forum.confluent.io/u/rick)\
**Post date:** [6 March 2022 20:32 UTC](https://forum.confluent.io/t/query-with-rolling-values-from-last-record/4361/2 "2022-03-06T20:32:03Z")

</div>

@almindor I’m not 100% certain I understand the desired behavior from your example, however, does the `LATEST_BY_OFFSET` aggregate function solve your need?

> **[ksqlDB Aggregate Functions - ksqlDB Documentation](https://docs.ksqldb.io/en/latest/developer-guide/ksqldb-reference/aggregate-functions/#latest_by_offset)**
>
> Aggregate functions to use in SQL statements and queries

---

<div class="post-metadata">

**Author:** ![almindor](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/almindor/32/1597_2.png) [@almindor](https://forum.confluent.io/u/almindor)\
**Post date:** [8 March 2022 18:06 UTC](https://forum.confluent.io/t/query-with-rolling-values-from-last-record/4361/3 "2022-03-08T18:06:33Z")

</div>

Yes, after a lot of trying I was able to do this using a triple join + group by combination. It’s not optimal since the joins add up to the window time but it works.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/confluentcommunity/original/1X/c49438c90c9df282e9996fdf6971be890c71b65a.svg) [@system](https://forum.confluent.io/u/system)\
**Post date:** [15 March 2022 18:07 UTC](https://forum.confluent.io/t/query-with-rolling-values-from-last-record/4361/4 "2022-03-15T18:07:11Z")

</div>

Closed on 15 March 2022 18:07 UTC

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.