Keep Every Reading While Looking at Its Neighbours

Botie has several readings for each pump. A grouped average can describe each pump, but the technician also needs the latest individual reading and wants to compare it with the one before it. Grouping everything into one average loses those individual rows.

A window function calculates across related rows while keeping a result attached to each row. We will first decide which rows belong together, then put those rows in an order that gives “latest” a precise meaning.

Order each pump’s readings, number them within the pump, and retain position one.

[
  {
    "asset_id": "P7",
    "reading_id": 3,
    "value": 24
  },
  {
    "asset_id": "P8",
    "reading_id": 4,
    "value": null
  }
]

PARTITION BY asset_id means each pump has its own window population. Without it, one global numbering could select the latest reading in the whole table and omit other pumps.

Unlike GROUP BY, the window annotation leaves each reading row available. Its value, ID and timestamp remain connected to the same observation.

Partitioning determines which rows may influence each other. It does not sort the final report.

ROW_NUMBER assigns one position per row within the window order. We order measured_at descending, then reading_id descending. If two readings share a timestamp, the larger ID wins under this lesson’s explicit policy.

The ID is a tie breaker, not proof that the physical measurement happened later. A production system should choose a rule that matches its event identity and revision policy.

Window ORDER BY establishes row positions. The outer ORDER BY independently controls the displayed report.

CREATE TABLE readings(reading_id INTEGER PRIMARY KEY, asset_id TEXT NOT NULL, measured_at TEXT NOT NULL, value INTEGER);
INSERT INTO readings VALUES (1,'P7','2026-01-01 08:00:00',20),(2,'P8','2026-01-01 08:00:00',50),(3,'P7','2026-01-01 09:00:00',24),(4,'P8','2026-01-01 09:00:00',NULL);

WITH numbered AS (
  SELECT reading_id, asset_id, value,
         ROW_NUMBER() OVER (PARTITION BY asset_id ORDER BY measured_at DESC, reading_id DESC) AS rn
  FROM readings
)
SELECT asset_id, reading_id, value, rn FROM numbered ORDER BY asset_id, rn;

Window functions operate after the input rows have passed the relevant WHERE and grouping stages. SQLite does not let WHERE in the same query directly filter ROW_NUMBER(). Put the annotation in a CTE or subquery, then filter its result.

Be careful with filters placed before the window. Restricting the source to a recent day changes what LAG can see at that day’s first reading. If the previous reading must come from earlier history, compute it before restricting the displayed rows.

The stage where a filter runs changes the rows available to the window.

ROW_NUMBER gives tied rows different positions. RANK gives peers the same position and leaves gaps after ties. DENSE_RANK gives peers the same position without those gaps. With values 90, 90 and 70 in descending order, RANK gives 1, 1 and 3; DENSE_RANK gives 1, 1 and 2.

For a competition-style value rank, order by the value alone if equal values should remain peers. Adding a unique ID to that ranking order breaks the tie. For selecting one latest row, that deterministic tie break is exactly what we want.

The function and ordering must express the requested tie policy together.

LAG(value) reads an earlier row in the partition’s order. It is useful for a reading-to-reading comparison. The first row has no predecessor and receives NULL unless a default was supplied.

An earlier row can itself contain NULL, so a NULL lag value does not always mean “there was no previous record.” Keep the previous reading’s ID too if the distinction matters.

Subtracting a previous value measures change between observations. It is not automatically a rate per hour.

CREATE TABLE readings(reading_id INTEGER PRIMARY KEY, asset_id TEXT NOT NULL, measured_at TEXT NOT NULL, value INTEGER);
INSERT INTO readings VALUES (1,'P7','2026-01-01 08:00:00',20),(2,'P8','2026-01-01 08:00:00',50),(3,'P7','2026-01-01 09:00:00',24),(4,'P8','2026-01-01 09:00:00',NULL);

SELECT asset_id, reading_id, value,
       LAG(value) OVER (PARTITION BY asset_id ORDER BY measured_at, reading_id) AS previous_value
FROM readings ORDER BY asset_id, measured_at, reading_id;

A MAX(measured_at) query joined back to the original table can return two rows when two readings share the maximum time. That might be correct for a request for all latest-time observations, but not for this one-row-per-pump contract.

The workshop first sorts complete records, then adds a row number and selects position one. New timestamps and ties should change the winner through those operations, without hard-coded reading IDs.

Inspect the sorted records when a winner looks wrong. The numbering cannot repair the wrong order.

Row numbers, ranks and LAG describe positions or neighbours. Running totals and rolling averages also need a decision about which range of nearby rows contributes to each answer.

That range is a window frame. The next lesson will use tied timestamps to show why an implicit frame can produce a result different from the row-by-row total we expected.

A partition defines the group of related rows. A frame can select a smaller portion of that partition for the current calculation.