# Latest data by Id

**URL:** <https://community.questdb.com/t/latest-data-by-id/128>\
**Category:** Community\
**Tags:** question\
**Created:** [July 26, 2024, 2:35pm UTC](https://community.questdb.com/t/latest-data-by-id/128 "2024-07-26T14:35:16Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vadym\_Kurinnyi](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/vadym_kurinnyi/32/74_2.png) [@Vadym\_Kurinnyi](https://community.questdb.com/u/Vadym_Kurinnyi)\
**Post date:** [July 26, 2024, 2:35pm UTC](https://community.questdb.com/t/latest-data-by-id/128/1 "2024-07-26T14:35:16Z")

</div>

```auto
CREATE TABLE counterData (
  counterId INT,
  timestamp TIMESTAMP,
  value DOUBLE
);

SELECT * FROM counterData
LATEST ON timestamp PARTITION BY counterId;

```

Is it a way to have kind of latestCounterData to get latest data by counterId?  
I’m not able to run LATEST ON cuz it will scan a lot of data.  
I used to have a sql trigger in MSSQL.

---

<div class="post-metadata">

**Author:** ![javier](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/javier/32/29_2.png) [@javier](https://community.questdb.com/u/javier)\
**Post date:** [July 26, 2024, 3:04pm UTC](https://community.questdb.com/t/latest-data-by-id/128/2 "2024-07-26T15:04:53Z")

</div>

Hey Vadym,

If I understood it well, you need what `LATEST ON` provides, but you find `LATEST ON` not performant enough?

In your example you are using an `INT` column. If you use instead a `SYMBOL` column, `LATEST ON` might perform better.

When you are partitioning by a single `SYMBO`L column, `LATEST ON` knows how many different values you have in your column, so it can start scanning the table by reverse timestamp, and stop scanning the moment it has found a row for each different column value.

If you are not using a `SYMBOL`, `LATEST ON` needs to do a full scan to find all possible values.

In some cases, even when using a `SYMBOL` column, it might be the case some values are very rare, and you only get one once in a while, or even you might have values that are not in use anymore but were at some point. In that case, `LATEST ON` needs to scan a big chunk of data. If your use case can afford it, it is a good idea to set a maximum timestamp using `WHERE`, as in

```auto
SELECT * FROM counterData
WHERE timestamp > dateadd('M', -1, now())
LATEST ON timestamp PARTITION BY counterId;

```

So any symbols not present in the past month, for example, will be ignored.

---

<div class="post-metadata">

**Author:** ![Vadym\_Kurinnyi](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/vadym_kurinnyi/32/74_2.png) [@Vadym\_Kurinnyi](https://community.questdb.com/u/Vadym_Kurinnyi)\
**Post date:** [July 26, 2024, 3:30pm UTC](https://community.questdb.com/t/latest-data-by-id/128/3 "2024-07-26T15:30:43Z")

</div>

Thank you very much for your answer. I will try to use `SYMBOL` instead of `INT`. Currently `dateadd('M', -1, now())`I takes 1.5 sec. I do preaggregation on the data so it’s crucial to be fast.

---

<div class="post-metadata">

**Author:** ![javier](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/javier/32/29_2.png) [@javier](https://community.questdb.com/u/javier)\
**Post date:** [July 29, 2024, 7:33am UTC](https://community.questdb.com/t/latest-data-by-id/128/4 "2024-07-29T07:33:47Z")

</div>

Perfect. Let us know if it worked 🙂