Which Two Visits Took the Longest?

Botie is asked to show the two longest recorded inspections. The first two rows currently happen to include a long visit, but the table is a record collection, not a ranked queue.

We need a ranking rule before taking a small portion. We also need to decide what a tie means so the shortlist does not change simply because the database chose a different way to read the same records.

Order by minutes descending and inspection_id ascending, then take two records.

[
  {
    "inspection_id": 101,
    "minutes": 30
  },
  {
    "inspection_id": 104,
    "minutes": 20
  }
]

The metric is recorded inspection minutes, so descending order puts the longest duration first. A text sort would ask a different question, and dates need a representation that sorts according to their intended timeline.

Our schema stores minutes as numeric values. Unknown durations are excluded from this ranked report because the supervisor asked for recorded durations; they belong in a separate missing-data report.

The shortlist is only as meaningful as the metric and population it ranks.

Two visits can have equal durations. Add inspection_id ascending after minutes descending so the smaller ID comes first within a tie. Each sort term has its own direction.

The final key should uniquely distinguish the rows that could reach the limit boundary. Adding a nonunique description as the final term can still leave an unresolved tie. A stable tie breaker produces reproducibility, not a claim that a smaller ID is more important.

The tie rule is a report convention. State it so the reader can interpret the shortlist.

LIMIT asks for a maximum number of result rows. Without a specified order, it does not identify the largest values. ORDER BY supplies that order before the result is limited.

The teaching workshop exposes two components because swapping them makes a visible difference. The SQL planner may implement an efficient top-k algorithm internally; the logical result must still match the requested ordering and limit.

The physical algorithm is free to differ from our visible sorting demonstration.

CREATE TABLE assets (
  asset_id TEXT PRIMARY KEY NOT NULL,
  site TEXT NOT NULL,
  kind TEXT NOT NULL,
  active INTEGER NOT NULL CHECK (active IN (0, 1))
);
CREATE TABLE inspections (
  inspection_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  minutes INTEGER CHECK (minutes >= 0),
  finding TEXT
);
CREATE TABLE readings (
  reading_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  temperature REAL
);
INSERT INTO assets VALUES
 ('P1','north','pump',1), ('P2','north','fan',1),
 ('P3','south','pump',0), ('P4','south','pump',1);
INSERT INTO inspections VALUES
 (101,'P1',30,'loose'), (102,'P1',NULL,NULL),
 (103,'P2',0,'clear'), (104,'P3',20,'clear'),
 (105,'P2',10,'loose'), (106,'P1',15,'loose');
INSERT INTO readings VALUES
 (1,'P1',70.0), (2,'P1',80.0), (3,'P2',NULL), (4,'P3',65.0);

SELECT inspection_id, minutes FROM inspections
WHERE minutes >= 0
ORDER BY minutes DESC, inspection_id ASC;

LIMIT 2 returns at most two rows even when another visit has the same duration as the second. A report asking for every visit tied at the boundary needs a different construction. Later ranking lessons distinguish row numbers from ranks.

This lesson deliberately requests exactly two rows when at least two qualify, using the stated ID tie rule. Do not infer a policy about equal priority from the SQL limit alone.

The required result shape must be clear before testing the query.

Database defaults differ for the placement of NULL in ordering. SQLite normally places NULL before non-null values in ascending order; PostgreSQL’s default is different. Both support explicit null placement in their documented forms, but our query first excludes unknown durations.

LIMIT is available in SQLite and PostgreSQL. Other systems may use FETCH or TOP. Keep the underlying idea of ordered truncation separate from a particular dialect’s syntax.

A query that moves between databases deserves a test with ties and missing values.

OFFSET can skip a number of ordered result rows, but newly inserted records can shift later pages. A bookmark based on an actual stable sort key is often a better way to continue a changing stream. We will examine query planning and application access later.

For the workshop, first reverse the source row order. The answer should remain unchanged. Then introduce equal longest durations and check the tie breaker.

A repeatable query depends on both its ordering rule and the data snapshot being queried.