Get Latest N Records Per Partition

Retrieve the most recent N rows for each distinct partition value (e.g., latest 5 trades per symbol, last 10 readings per sensor). While LATEST ON returns only the single most recent row per partition, this pattern extends it to get multiple recent rows per partition.

Problem: Need Multiple Recent Rows Per Group​

You want to get the latest N rows for each distinct value in a column. For example:

  • Latest 5 trades for each trading symbol
  • Last 10 sensor readings per device
  • Most recent 3 log entries per service

LATEST ON only returns one row per partition:

-- Gets only 1 latest row per symbol
SELECT * FROM trades
LATEST ON timestamp PARTITION BY symbol;

But you need multiple rows per symbol.

Solution: Use ROW_NUMBER() Window Function​

Use row_number() to rank rows within each partition, then filter to keep only the top N:

Get latest 5 trades for each symbolDemo this query
WITH ranked AS (
  SELECT
    *,
    row_number() OVER (PARTITION BY symbol ORDER BY timestamp DESC) as rn
  FROM trades
  WHERE timestamp >= dateadd('d', -1, now())
)
SELECT timestamp, symbol, side, price, amount
FROM ranked
WHERE rn <= 5
ORDER BY symbol, timestamp DESC;

This returns up to 5 most recent trades for each symbol from the last day.

How It Works​

The query uses a two-step approach:

  1. Ranking step (CTE):

    • row_number() OVER (...): Assigns sequential numbers to rows within each partition
    • PARTITION BY symbol: Separate ranking for each symbol
    • ORDER BY timestamp DESC: Newest rows get lower numbers (1, 2, 3, ...)
    • Result: Each row gets a rank within its symbol group

  2. Filtering step (outer query):

    • WHERE rn <= 5: Keep only rows ranked 1-5 (the 5 most recent)
    • ORDER BY symbol, timestamp DESC: Sort final results

Understanding row_number()​

row_number() assigns a unique sequential number within each partition:

timestampsymbolprice(row number)
10:03:00BTC-USDT630001 (newest)
10:02:00BTC-USDT629002
10:01:00BTC-USDT628003
10:03:30ETH-USDT31001 (newest)
10:02:30ETH-USDT30952

With WHERE rn <= 3, we keep rows 1-3 for each symbol.

Adapting the Query​

Different partition columns:

-- Latest 10 per sensor_id
PARTITION BY sensor_id

-- Latest 5 per combination of symbol and exchange
PARTITION BY symbol, exchange

-- Latest N per user_id
PARTITION BY user_id

Different sort orders:

-- Oldest N rows per partition
ORDER BY timestamp ASC

-- Highest prices first
ORDER BY price DESC

-- Alphabetically
ORDER BY name ASC

Dynamic N value:

-- Latest N trades where N is specified by user
DECLARE @limit := 10

WITH ranked AS (
  SELECT *, row_number() OVER (PARTITION BY symbol ORDER BY timestamp DESC) as rn
  FROM trades
  WHERE timestamp >= dateadd('d', -1, now())
)
SELECT * FROM ranked WHERE rn <= @limit;

Include additional filtering:

Latest 5 buy orders per symbolDemo this query
WITH ranked AS (
  SELECT
    *,
    row_number() OVER (PARTITION BY symbol ORDER BY timestamp DESC) as rn
  FROM trades
  WHERE timestamp >= dateadd('d', -1, now())
    AND side = 'buy'  -- Additional filter before ranking
)
SELECT timestamp, symbol, side, price, amount
FROM ranked
WHERE rn <= 5;

Show rank in results:

WITH ranked AS (
  SELECT *, row_number() OVER (PARTITION BY symbol ORDER BY timestamp DESC) as rn
  FROM trades
  WHERE timestamp >= dateadd('d', -1, now())
)
SELECT timestamp, symbol, price, rn as rank
FROM ranked
WHERE rn <= 5;

Alternative: Use Negative LIMIT​

For a simpler approach when you need the latest N rows total (not per partition), use negative LIMIT:

Latest 100 trades overall (all symbols)Demo this query
SELECT * FROM trades
WHERE symbol = 'BTC-USDT'
ORDER BY timestamp DESC
LIMIT 100;

Or more efficiently with QuestDB's negative LIMIT feature:

Latest 100 trades using negative LIMITDemo this query
SELECT * FROM trades
WHERE symbol = 'BTC-USDT'
LIMIT -100;

But this doesn't work per partition - it returns 100 total rows, not 100 per symbol.

Performance Optimization​

Filter by timestamp first:

-- Good: Reduces dataset before windowing
WITH ranked AS (
  SELECT *, row_number() OVER (PARTITION BY symbol ORDER BY timestamp DESC) as rn
  FROM trades
  WHERE timestamp >= dateadd('h', -24, now())  -- Filter early
)
SELECT * FROM ranked WHERE rn <= 5;

-- Less efficient: Windows over entire table
WITH ranked AS (
  SELECT *, row_number() OVER (PARTITION BY symbol ORDER BY timestamp DESC) as rn
  FROM trades  -- No filter
)
SELECT * FROM ranked WHERE rn <= 5 AND timestamp >= dateadd('h', -24, now());

Limit partitions:

-- Process only specific symbols
WHERE timestamp >= dateadd('d', -1, now())
  AND symbol IN ('BTC-USDT', 'ETH-USDT', 'SOL-USDT')

Top N with Aggregates​

Combine with aggregates to get summary statistics for top N:

Average price of latest 10 trades per symbolDemo this query
WITH ranked AS (
  SELECT
    timestamp,
    symbol,
    price,
    row_number() OVER (PARTITION BY symbol ORDER BY timestamp DESC) as rn
  FROM trades
  WHERE timestamp >= dateadd('d', -1, now())
)
SELECT
  symbol,
  count(*) as trade_count,
  avg(price) as avg_price,
  min(price) as min_price,
  max(price) as max_price
FROM ranked
WHERE rn <= 10
GROUP BY symbol;

Comparison with LATEST ON​

FeatureLATEST ONrow_number() + Filter
Rows per partitionExactly 1Any number (N)
PerformanceVery fast (optimized)Moderate (requires ranking)
FlexibilityLimitedHigh (custom ordering, filtering)
Use caseSingle latest valueMultiple recent values

When to use LATEST ON:

-- Get current price for each symbol (1 row per symbol)
SELECT * FROM trades LATEST ON timestamp PARTITION BY symbol;

When to use row_number():

-- Get latest 5 trades for each symbol (up to 5 rows per symbol)
WITH ranked AS (
  SELECT *, row_number() OVER (PARTITION BY symbol ORDER BY timestamp DESC) as rn
  FROM trades
)
SELECT * FROM ranked WHERE rn <= 5;
Combining with LATEST ON

For very large tables, use LATEST ON to reduce the dataset first, then apply row_number():

WITH recent AS (
  -- Get latest 1000 rows overall
  SELECT * FROM trades
  ORDER BY timestamp DESC
  LIMIT 1000
)
, ranked AS (
  SELECT *, row_number() OVER (PARTITION BY symbol ORDER BY timestamp DESC) as rn
  FROM recent
)
SELECT * FROM ranked WHERE rn <= 5;

This approach is faster when you only need recent data across all partitions.

Row Count

The number of rows returned is N × number_of_partitions. If you have 100 symbols and request top 5, you'll get up to 500 rows. Some partitions may have fewer than N rows if insufficient data exists.

Related Documentation