"Top-1 Per Group”: Get the latest row per deviceid in a time range

Question

I'm using GridDB TimeSeries and need, for a given time range, the latest row per deviceid (i.e., top-1 by timestamp within each device), returned in one query.

Expected output (example for two devices over a day):

deviceid  ts                        temperature
--------  ------------------------  -----------
dev-001   2025-09-01T09:00:00Z      24.9
dev-002   2025-09-01T09:00:00Z      27.1

---

My Schema (TimeSeries):

ts (TIMESTAMP, row key)
deviceid (STRING)
temperature (DOUBLE)

Minimal reproducible data (SQL mode):

INSERT INTO TSDB (ts, deviceid, temperature) VALUES
  (TIMESTAMP('2025-09-01T00:00:00Z'),'dev-001',25.4),
  (TIMESTAMP('2025-09-01T01:00:00Z'),'dev-001',26.1),
  (TIMESTAMP('2025-09-01T02:00:00Z'),'dev-001',27.8),
  (TIMESTAMP('2025-09-01T09:00:00Z'),'dev-001',24.9),    
  (TIMESTAMP('2025-09-01T00:00:00Z'),'dev-002',23.5),
  (TIMESTAMP('2025-09-01T01:00:00Z'),'dev-002',24.0),
  (TIMESTAMP('2025-09-01T02:00:00Z'),'dev-002',22.8),
  (TIMESTAMP('2025-09-01T09:00:00Z'),'dev-002',27.1);

---

What I tried:

A) TQL (works for one device at a time)

 -- latest row for a single device
        SELECT * 
        WHERE deviceid = 'dev-001'
          AND ts >= TIMESTAMP('2025-09-01T00:00:00Z')
          AND ts <  TIMESTAMP('2025-09-02T00:00:00Z')
        ORDER BY ts DESC
        LIMIT 1;

But I’m looking for one result per device in a single query.

B) SQL with subquery join (top-1 per group pattern) Trying to compute MAX(ts) per deviceid, then join back to get the full row:

SELECT t.*
FROM TSDB t
JOIN (
  SELECT deviceid, MAX(ts) AS max_ts
  FROM TSDB
  WHERE ts >= TIMESTAMP('2025-09-01T00:00:00Z')
    AND ts <  TIMESTAMP('2025-09-02T00:00:00Z')
  GROUP BY deviceid
) m
  ON t.deviceid = m.deviceid
 AND t.ts       = m.max_ts
ORDER BY t.deviceid;

If joins/subqueries aren’t supported in GridDB SQL, what’s the recommended approach?

---

Questions:

• Is there a single SQL or TQL query to return the latest row per deviceid in a time range?
• If not, what’s the recommended pattern in GridDB?
• Any performance tips or indexing suggestions for large numbers of devices?

---

Environment

• GridDB Cloud (Free plan)
• Console (SQL + TQL)

---

SQL Output:

TQL Output:

Answer 1

According to the docs (griddb.org/docs-en/manuals/GridDB_SQL_Reference.html#from), subqueries are not allowed in JOIN but are allowed in FROM, so you should be able to switch table and subquery:

SELECT t.*
FROM 
(
  SELECT deviceid, MAX(ts) AS max_ts
  FROM tsdb
  WHERE ts >= TIMESTAMP('2025-09-01T00:00:00Z')
    AND ts <  TIMESTAMP('2025-09-02T00:00:00Z')
  GROUP BY deviceid
) m
JOIN tsdb t
  ON t.deviceid = m.deviceid
 AND t.ts       = m.max_ts
ORDER BY t.deviceid;

An alternative is to use NOT EXISTS (griddb.org/docs-en/manuals/GridDB_SQL_Reference.html#exists), so as to select the rows for with NOT EXISTS a row with the same deviceid, but a newer ts.

SELECT t.*
FROM tsdb t
WHERE ts >= TIMESTAMP('2025-09-01T00:00:00Z')
  AND ts <  TIMESTAMP('2025-09-02T00:00:00Z')
AND NOT EXISTS
(
  SELECT *
  FROM tsdb t2
  WHERE t2.ts >= TIMESTAMP('2025-09-01T00:00:00Z')
    AND t2.ts <  TIMESTAMP('2025-09-02T00:00:00Z')
  AND t2.deviceid = t.deviceid
  AND t2.ts > t.ts
)
ORDER BY t.deviceid;