Garder une ligne par clé

Fait partie de la section Au-delà des bases du Journey SQL de Coddy — leçon 26 sur 27.

Vous aurez souvent un tableau où une clé (un client, un capteur, un produit) apparaît plusieurs fois et vous ne voulez garder que la plus récente, ou celle avec le score le plus élevé.

Utilisez ROW_NUMBER() de la même manière que pour le top-N, puis gardez 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

Le PARTITION BY indique « un classement par cette clé » ; le ORDER BY indique « le plus récent en premier ». La ligne 1 de chaque partition est la ligne que vous souhaitez conserver.

Défi

Moyen

Tables et colonnes disponibles :
readings : sensor_id, recorded_at, value

Pour chaque capteur ayant au moins 3 relevés, retournez son unique relevé de valeur la plus élevée. Si deux relevés sont à égalité sur value, conservez le plus récent.

Affichez sensor_id, recorded_at, value, ordonné par sensor_id.

Aide-mémoire

Utilisez ROW_NUMBER() avec PARTITION BY pour ne conserver que la ligne la plus récente (ou la meilleure) par clé, puis filtrez 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 — réinitialise le classement pour chaque clé unique
ORDER BY ... DESC — place la ligne souhaitée en premier (la plus récente, la plus élevée, etc.)
WHERE rn = 1 — ne conserve que cette première ligne par partition

Essayez vous-même

WITH ranked AS (
    -- ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY value DESC, recorded_at DESC)
    -- COUNT(*) OVER (PARTITION BY sensor_id) pour filtrer les capteurs avec >= 3 lectures
)
SELECT sensor_id, recorded_at, value
FROM ranked
WHERE rn = 1 AND total_readings >= 3
ORDER BY sensor_id