← All workshopsRead Keep Every Reading While Looking at Its Neighbours

SQL

Keep the latest complete record for each pump

The latest timestamp now has two reading IDs

Botie’s latest-reading report returns two winners when timestamps tie. The intended report needs one complete row per pump under a stated tie rule.

BOTIE

Readings 3 and 5 for P7 share the latest time. The specified secondary order is reading_id descending. Which complete row should remain?
The inputs we are comparing

Timestamped reading records

Two independent reading histories

[
  {
    "reading_id": 1,
    "asset_id": "P7",
    "measured_at": "2026-01-01 08:00:00",
    "value": 20
  },
  {
    "reading_id": 2,
    "asset_id": "P8",
    "measured_at": "2026-01-01 08:00:00",
    "value": 50
  },
  {
    "reading_id": 3,
    "asset_id": "P7",
    "measured_at": "2026-01-01 09:00:00",
    "value": 24
  },
  {
    "reading_id": 4,
    "asset_id": "P8",
    "measured_at": "2026-01-01 09:00:00",
    "value": null
  }
]

The latest timestamp is shared by two records

[
  {
    "reading_id": 3,
    "asset_id": "P7",
    "measured_at": "2026-01-01 09:00:00",
    "value": 24
  },
  {
    "reading_id": 5,
    "asset_id": "P7",
    "measured_at": "2026-01-01 09:00:00",
    "value": 29
  }
]
Result for two independent reading histories
[
  {
    "asset_id": "P7",
    "reading_id": 3,
    "value": 24
  },
  {
    "asset_id": "P8",
    "reading_id": 4,
    "value": null
  }
]

For a list use brackets, such as [2, 4]. For a matrix put each row in brackets. Text answers can stay as text.

New assets arrive out of order with a tied missing winner

This check uses the pieces from “Keep the winner without losing its missing value”.

BOTIE

Choose the latest row per asset by measured_at descending and reading_id descending. P4’s latest timestamp ties, and the larger reading ID has a NULL value. Keep that winning record.
Timestamped reading records
[
  {
    "reading_id": 10,
    "asset_id": "P4",
    "measured_at": "2026-03-01 09:00:00",
    "value": 18
  },
  {
    "reading_id": 20,
    "asset_id": "P2",
    "measured_at": "2026-03-01 08:00:00",
    "value": 5
  },
  {
    "reading_id": 11,
    "asset_id": "P4",
    "measured_at": "2026-03-01 09:00:00",
    "value": null
  },
  {
    "reading_id": 19,
    "asset_id": "P2",
    "measured_at": "2026-03-01 10:00:00",
    "value": 8
  }
]

Use a number, text, or an array matching the requested result. For a matrix, put each row in brackets. Saving your prediction runs your current construction on these inputs and checks the earlier examples too.

Build a construction in the workspace above first. This case will test those pieces.

How would filtering unknown values before numbering change the meaning of latest?

Decide the population before the window, then keep the selected record’s values and identity together.

The timestamp strings are normalised UTC values; mixed timezone strings require normalisation before lexicographic ordering.

Work through the SQL challenges →
Continue the courseWhich Readings Belong in This Rolling Total? →
← How Far Does the Failed Supply Line Reach?