CODEWITHSUNDEEP
questdb
Wannabe
Wannabe

Reputation: 111

Retrieving the row with the greatest timestamp in questDB

I'm currently running QuestDB 6.1.2 on linux. How do I get the row with maximum value from a table? I have tried the following on a test table with around 5 million rows:

  1. select * from table where cast(timestamp as symbol) in (select cast(max(timestamp) as symbol) from table );
  2. select * from table inner join (select max(timestamp) mm from table ) on timestamp >= mm
  3. select * from table where timestamp = max(timestamp)
  4. select * from table where timestamp = (select max(timestamp) from table )

where 1 is correct but runs in ~5s, 2 is correct and runs in ~500ms but looks unnecessarily verbose for a query, 3 compiles but returns an empty table, and 4 is incorrect syntax although that's how sql usually does it

Upvotes: 1

Views: 450

Answers (1)

Wannabe
Wannabe

Reputation: 111

select * from table limit -1 works. QuestDB returns rows sorted by timestamp as default, and limit -1 takes the last row, which happens to be the row with the greatest timestamp. To be explicit about ordering by timestamp, select * from table order by timestamp limit -1 could be used instead. This query runs in around 300-400ms on the same table.

As a side note, the third query using timestamp=max(timestamp) doesn't work yet since QuestDB does not support subqueries in where yet (questDB 6.1.2).

Upvotes: 3

Related Questions