# Select nearest timestamp from multiple tables

**URL:** <https://community.questdb.com/t/select-nearest-timestamp-from-multiple-tables/885>\
**Category:** Community\
**Tags:** sql, question\
**Created:** [July 13, 2025, 9:39pm UTC](https://community.questdb.com/t/select-nearest-timestamp-from-multiple-tables/885 "2025-07-13T21:39:27Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jmthomas](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/jmthomas/32/462_2.png) [@jmthomas](https://community.questdb.com/u/jmthomas)\
**Post date:** [July 13, 2025, 9:39pm UTC](https://community.questdb.com/t/select-nearest-timestamp-from-multiple-tables/885/1 "2025-07-13T21:39:27Z")

</div>

I’m trying to select from multiple tables values closest (\<=) to the given timestamp. For example in the [demo](https://demo.questdb.io/index.html) how would I select a few columns from one table and a few columns from another where the timestamp is closest to X. Sorry I’m not as familiar with the demo table data.

---

<div class="post-metadata">

**Author:** ![nwoolmer](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/nwoolmer/32/24_2.png) [@nwoolmer](https://community.questdb.com/u/nwoolmer)\
**Post date:** [July 13, 2025, 9:43pm UTC](https://community.questdb.com/t/select-nearest-timestamp-from-multiple-tables/885/2 "2025-07-13T21:43:42Z")

</div>

Hi @jmthomas

You can use `ASOF JOIN`: [ASOF JOIN keyword | QuestDB](https://questdb.com/docs/reference/sql/asof-join/)

If you want to key it i.e. nearest row for each symbol, you can use `ON`.

```sql
SELECT * FROM
(
    (
    SELECT dateadd('d', -1, now()) as t
    UNION
    SELECT dateadd('d', -2, now()) as t
    ) ORDER BY t
)
ASOF JOIN trades;

```

| t | symbol | side | price | amount | timestamp |
| --- | --- | --- | --- | --- | --- |
| 2025-07-11T21:41:16.361236Z | ETH-USD | buy | 2973.57 | 1.681951 | 2025-07-11T21:41:16.244999Z |
| 2025-07-12T21:41:16.361236Z | ETH-USD | buy | 2937.68 | 0.745448 | 2025-07-12T21:41:16.357000Z |

If it will just be a single row, then you can just filter for `WHERE timestamp <= t ORDER BY timestamp DESC LIMIT 1`.

---

<div class="post-metadata">

**Author:** ![jmthomas](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/jmthomas/32/462_2.png) [@jmthomas](https://community.questdb.com/u/jmthomas)\
**Post date:** [July 14, 2025, 10:55pm UTC](https://community.questdb.com/t/select-nearest-timestamp-from-multiple-tables/885/3 "2025-07-14T22:55:49Z")

</div>

Yeah I think I just needed to experiment with it a little. What I was really looking for is something like this:

`select t1.timestamp, VALUE1, VALUE2 from TABLE1 as t1 ASOF JOIN TABLE2 LIMIT -1`

Where VALUE1 comes from TABLE1 and VALUE2 comes from TABLE2. The `LIMIT -1` gives me the latest value from TABLE1 which is matched to the closest (\<=) value in TABLE2. Please correct me if I’m wrong here.

---

<div class="post-metadata">

**Author:** ![jmthomas](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/jmthomas/32/462_2.png) [@jmthomas](https://community.questdb.com/u/jmthomas)\
**Post date:** [July 14, 2025, 10:55pm UTC](https://community.questdb.com/t/select-nearest-timestamp-from-multiple-tables/885/4 "2025-07-14T22:55:54Z")

</div>

I also needed the `WHERE` clause limiting the time:

select t1.timestamp, VALUE1, VALUE2 from TABLE1 as t1 ASOF JOIN TABLE2 where t1.timestamp \< ‘2025-07-14T22:20:00’ LIMIT -1

---

<div class="post-metadata">

**Author:** ![nwoolmer](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/nwoolmer/32/24_2.png) [@nwoolmer](https://community.questdb.com/u/nwoolmer)\
**Post date:** [July 15, 2025, 10:33am UTC](https://community.questdb.com/t/select-nearest-timestamp-from-multiple-tables/885/5 "2025-07-15T10:33:32Z")

</div>

Yes, if you are just picking a single row, that’s the way to do it.

If you need to batch up a bunch of these ‘point queries’, you have two options:

1. Just stream them using Postgres Wire, with the right settings it will get pipelined and sent back in batches.
2. Combine them into one query with `UNION`. This only support a limited number by default (maybe 100 or so). It can be configured to more, but may be more trouble than its worth.