Oracle Database / Intermediate: application SQL and evidence / Lesson 8

Analytic windows and deterministic ranking

Retain shipment detail while calculating ranks, running totals and previous-event comparisons.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Choose partitions, order and window frames independently.
Explain ties and filter analytic output through an outer query.

Analytics preserve detail rows

GROUP BY collapses a population to one row per group. An analytic SUM(...) OVER (...) instead adds a value to each detail row. PARTITION BY defines independent populations, and its meaning is unrelated to physical table partitioning. A per-customer running charge begins again for each customer even if all rows share one physical table. Name the required detail grain before adding a window, because joins and filters happen before analytics. A duplicated shipment from an earlier scan join remains duplicated and will contaminate the analytic calculation rather than being automatically repaired.

State the frame when accumulating

For a row-by-row running total, use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW with a total order. The fixture contains two equal booking times, so shipment_id breaks the tie. Omitting the explicit frame can produce a logical RANGE frame whose peer rows receive the same accumulated value. That is useful for some questions but differs from a sequential ledger. The final query ORDER BY is still necessary: the order inside OVER defines the calculation, not a promise about presentation order. These are separate contracts even when they use identical expressions.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

WITH charges AS (
 SELECT 101 shipment_id, 7 customer_id, 10 charge FROM dual UNION ALL
 SELECT 102, 7, 5 FROM dual UNION ALL
 SELECT 103, 7, 8 FROM dual
)
SELECT shipment_id, charge,
 SUM(charge) OVER (PARTITION BY customer_id ORDER BY shipment_id
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_charge
FROM charges ORDER BY shipment_id;

Expected output

SHIPMENT_ID | CHARGE | RUNNING_CHARGE
101         | 10     | 10
102         | 5      | 15
103         | 8      | 23

The partition contains one customer; the explicit row frame accumulates 10, then 15, then 23.

Choose which kind of tie matters

ROW_NUMBER assigns distinct positions and needs a unique tie-breaker for repeatability. RANK gives tied values the same rank and leaves gaps afterward; DENSE_RANK gives tied values the same rank without those gaps. “Top three shipments” may mean exactly three records, all records in the three highest charge bands, or all records tied at third place. These are different business requests. Do not add a unique identifier to a DENSE_RANK ordering intended to group equal charges, because that destroys the desired ties. Use a separate final sort key for display when necessary.

Filter after the analytic stage

In the 19c teaching baseline, compute ROW_NUMBER in an inner query and filter its alias in an outer query. The same query block’s WHERE cannot select an analytic alias because WHERE logically precedes that calculation. The example returns the newest shipment per customer with explicit null placement and identifier tie-breaking. It assumes a NULL booking time should lose to known times. If business rules say such rows are invalid instead, reject or filter them deliberately before ranking. Do not silently change missing-value semantics merely to make the syntax shorter.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT customer_id, shipment_id, booked_at
FROM (
 SELECT customer_id, shipment_id, booked_at,
 ROW_NUMBER() OVER (PARTITION BY customer_id
 ORDER BY booked_at DESC NULLS LAST, shipment_id DESC) AS rn
 FROM shipments
)
WHERE rn = 1
ORDER BY customer_id;

At most one row per customer survives. The winning key is deterministic for a fixed input set.

Previous means previous in a declared order

LAG and LEAD compare adjacent ordered rows without self-joining every event. The previous scan time is meaningful only after defining shipment partitioning, timestamp ordering and a tie rule for simultaneous scans. A sequence generated at ingestion may capture arrival order rather than the physical event order reported by a device. Name which history the report reconstructs. Keep NULL behavior explicit and distinguish no previous row from a previous row whose value is missing when that distinction matters. Large sorts and partitions can consume memory and temporary space, so combine semantic review with plan evidence before scaling a report.

Common errors and fixes

ORA-30483

Why it happens: An analytic expression was placed in a query location that does not permit it.
How to fix it: Compute it in an inner SELECT and filter the resulting column outside.
Prevent it next time: Review the logical sequence of filtering, grouping and analytics.

Running totals jump across equal timestamps

Why it happens: The default logical frame groups peers together.
How to fix it: Choose an explicit ROWS frame with a deterministic order when sequential accumulation is intended.
Prevent it next time: Test repeated ordering values rather than unique timestamps only.

The newest shipment changes between executions

Why it happens: ROW_NUMBER ordering leaves a tie unresolved.
How to fix it: Add a stable unique tie-breaker while preserving the intended primary sort.
Prevent it next time: Specify tie semantics in the report acceptance criteria.

Check your understanding

1. For charges 20, 20 and 10 ordered descending, what are RANK and DENSE_RANK?

Show worked solution

RANK gives 1, 1, 3; DENSE_RANK gives 1, 1, 2 when ordering by charge alone. ROW_NUMBER gives three distinct positions, but the two twenties need an additional unique key for a stable assignment. Choose the function from the business treatment of ties.

2. Why is an ORDER BY inside OVER insufficient to promise output order?