lesson 40 Window functions · advanced

Latest row per group

Keep the most recent record for each group.

the idea

To keep the latest row per group, number each group’s rows newest first with ROW_NUMBER() and keep row 1. Order by the time the event happened, not the order it arrived, and add a tie-breaker. An as-of cutoff filters events before ranking.

Dataset: Logistics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Scroll one package’s scans. Run this. Scans for package 1 appear newest first by event_at—the same order you want when asking “where is it now?”

   step-1.sql Run

   sql›

   SELECT package_id, event_type, event_at, recorded_at
   FROM tracking_events
   WHERE package_id = 1
   ORDER BY event_at DESC, event_id DESC;

2. 2

   Mark the latest event per package. Run this. ROW_NUMBER() gives 1 to the newest scan in each package group; that row is the current status when ties are broken by event_id.

   step-2.sql Run

   sql›

   SELECT
     package_id,
     event_type,
     event_at,
     ROW_NUMBER() OVER (PARTITION BY package_id ORDER BY event_at DESC, event_id DESC) AS rn
   FROM tracking_events
   WHERE package_id <= 3;

3. 3

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

   SELECT package_id, event_type, event_at
   FROM tracking_events
   WHERE package_id = 1
   ORDER BY event_at DESC, event_id DESC;

common mistake

MAX(event_at) with GROUP BY returns the latest time but not the matching event_type; picking event_type alongside it is unreliable.