Keeping One Row per Key

Part of the Beyond the Basics section of Coddy's SQL journey — lesson 26 of 27.

You'll often have a table where a key (a customer, a sensor, a product) shows up multiple times and you only want to keep the latest, or the one with the highest score.

Use ROW_NUMBER() the same way you did for top-N, then keep rn = 1.

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY purchased_at DESC
           ) AS rn
    FROM purchases
)
SELECT * FROM ranked WHERE rn = 1

The PARTITION BY says "one ranking per this key"; the ORDER BY says "newest first". Row 1 in each partition is the row you want to keep.

Challenge

Medium

Available tables and columns:
readings: sensor_id, recorded_at, value

For each sensor that has at least 3 readings, return its single highest-value reading. If two readings tie on value, keep the more recent one.

Output sensor_id, recorded_at, value, ordered by sensor_id.

Cheat sheet

Use ROW_NUMBER() with PARTITION BY to keep only the latest (or best) row per key, then filter rn = 1:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY purchased_at DESC
           ) AS rn
    FROM purchases
)
SELECT * FROM ranked WHERE rn = 1

PARTITION BY — resets the ranking for each unique key
ORDER BY ... DESC — puts the desired row first (newest, highest, etc.)
WHERE rn = 1 — keeps only that top row per partition

Try it yourself

WITH ranked AS (
    -- ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY value DESC, recorded_at DESC)
    -- COUNT(*) OVER (PARTITION BY sensor_id) to filter sensors with >= 3 readings
)
SELECT sensor_id, recorded_at, value
FROM ranked
WHERE rn = 1 AND total_readings >= 3
ORDER BY sensor_id