Menu

PostgreSQL DISTINCT ON : une ligne par groupe

SELECT DISTINCT ON (clé) ... ORDER BY clé, tri garde la première ligne de chaque groupe, comme la dernière commande de chaque client. C'est propre à PostgreSQL.

Cette page contient des éditeurs exécutables. Modifiez, exécutez et voyez la sortie instantanément.

SELECT DISTINCT ON (expr) ... renvoie une ligne pour chaque valeur distincte de expr : la première ligne de ce groupe dans l'ordre de l'ORDER BY. Triez d'abord sur la clé du groupe, puis sur ce qui décide de la « première ». Pour la dernière commande de chaque client, cela donne ORDER BY customer, created_at DESC.

La requête renvoie trois lignes : la commande 2 pour asha, la 4 pour ben et la 5 pour chen, soit la plus récente de chaque client. DISTINCT ON est une extension de PostgreSQL. Les autres bases de données ont besoin de la forme avec une window function présentée plus bas.

Comment l'ORDER BY décide de la ligne gardée

PostgreSQL trie les lignes, puis garde la première ligne chaque fois que la valeur de DISTINCT ON change. Donc :

  • L'ORDER BY doit commencer par les expressions de DISTINCT ON, sinon PostgreSQL lève « SELECT DISTINCT ON expressions must match initial ORDER BY expressions ».
  • Sans ORDER BY au-delà de ces expressions, la ligne gardée est imprévisible et peut changer d'une exécution à l'autre.

Remplacez created_at DESC par total DESC et la même requête renvoie la plus grosse commande de chaque client :

La ligne d'asha est maintenant la commande 1 (40) et celle de ben la commande 3 (60).

Départager les égalités volontairement

Quand deux lignes sont à égalité sur la colonne de tri, l'une ou l'autre peut l'emporter. Ajoutez une dernière clé de tri unique, comme la clé primaire, pour que le résultat soit le même à chaque fois :

Les lignes 2 et 3 ont toutes les deux 95 points. id en dernière clé fait gagner la ligne 2.

Regrouper sur plusieurs colonnes

Listez plusieurs expressions pour obtenir une ligne par combinaison :

Chaque paire capteur et type garde sa dernière mesure : s1 humidity 40, s1 temp 22, s2 temp 19.

La version portable avec ROW_NUMBER

ROW_NUMBER() numérote les lignes à l'intérieur de chaque partition. Garder le numéro 1 donne le même résultat que DISTINCT ON, et cela fonctionne aussi dans MySQL 8, SQL Server et SQLite :

SELECT customer, id, total, created_at
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer ORDER BY created_at DESC) AS rn
    FROM orders o
) ranked
WHERE rn = 1;

Utilisez cette forme quand vous voulez les n premières lignes par groupe (rn <= 3) ou quand la requête doit tourner sur une autre base de données. La page sur les window functions explique PARTITION BY.

Le rendre rapide

DISTINCT ON trie les données d'entrée, donc sur une grosse table donnez-lui un index qui correspond à l'ORDER BY :

CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);

PostgreSQL peut alors lire les lignes dans l'ordre au lieu de les trier. Avec peu de groupes et beaucoup de lignes par groupe, une jointure LATERAL qui prend LIMIT 1 par client dans le même index lit en général moins de lignes. La page sur les jointures présente LATERAL. Comparez les deux avec EXPLAIN ANALYZE sur vos données.

Questions fréquentes

Quelle différence entre DISTINCT et DISTINCT ON ?

DISTINCT supprime les lignes identiques sur toutes les colonnes sélectionnées. DISTINCT ON (a) garde une ligne pour chaque valeur de a, et les autres colonnes viennent de la ligne qui arrive en premier dans le tri de ce groupe.

DISTINCT ON fait-il partie du SQL standard ?

Non. C'est une extension de PostgreSQL, donc elle ne fonctionne ni dans MySQL ni dans SQL Server. La version portable est ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) dans une sous-requête, filtrée sur le numéro de ligne 1.

Pourquoi ai-je l'erreur « SELECT DISTINCT ON expressions must match initial ORDER BY expressions » ?

L'ORDER BY doit commencer par les mêmes expressions que DISTINCT ON, dans le même ordre. Écrivez DISTINCT ON (customer) ... ORDER BY customer, created_at DESC, et pas ORDER BY created_at DESC tout seul.

DISTINCT ON peut-il renvoyer les 3 premières lignes par groupe ?

Non, il renvoie exactement une ligne par groupe. Pour les n premières, numérotez les lignes avec ROW_NUMBER() et gardez rn <= 3, ou utilisez une sous-requête LATERAL avec LIMIT 3.

Illustration des langages de programmation de Coddy

Apprendre à coder avec Coddy

Commencer