Why does the latest price date show an older high price?

Prepare the diagnostic input

The last published price in this supplied history is the row with the latest valid_from date. MAX(valid_from) and MAX(unit_cents) are independent calculations. A price decrease makes that pair inconsistent, even though every individual value came from the table.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE prices(id INTEGER PRIMARY KEY,sku TEXT,valid_from TEXT,unit_cents INTEGER);
INSERT INTO prices VALUES (1,'A','2026-09-01',2000),(2,'A','2026-10-01',1500);

Reproduce the misleading result and correct it

SELECT sku,MAX(valid_from) AS valid_from,MAX(unit_cents) AS unit_cents FROM prices GROUP BY sku ORDER BY sku;

WITH ranked AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY sku ORDER BY valid_from DESC,id DESC) AS rn FROM prices)
SELECT sku,valid_from,unit_cents FROM ranked WHERE rn=1 ORDER BY sku;

The October price is 1500 cents; 2000 cents belongs to September. The bad query displays October beside 2000. Rank complete rows by date and the explicit revision ID, then select one row, retaining the price attached to it.

Check the boundary before applying the fix

A later revision on the same effective date has ID 3 and price 1400. Under the fixture’s higher-ID revision rule it wins. Without that approved tie rule, equal dates need review rather than an arbitrary choice.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO prices VALUES (3,'A','2026-10-01',1400);

All rows are published prices available for selection. Future schedules and customer-specific price agreements require additional eligibility conditions.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.