DISTINCT ON et LATERAL

Deux fonctionnalités très pratiques de PostgreSQL pour les requêtes « le premier de chaque groupe » et « pour chaque ligne, une sous-requête ».

DISTINCT ON : un peu de sucre pour le top 1#

-- La dernière commande de chaque client, sans fonction de fenêtre
SELECT DISTINCT ON (client_id) client_id, id, total, cree_le
FROM   commandes
ORDER  BY client_id, cree_le DESC;

DISTINCT ON (x) garde la première ligne pour chaque valeur de x, selon l'ORDER BY (qui doit commencer par x).

LATERAL : une sous-requête qui voit la ligne courante#

-- Les 3 dernières commandes de chaque client
SELECT c.nom, last3.id, last3.total
FROM   clients c
CROSS  JOIN LATERAL (
    SELECT o.id, o.total
    FROM   commandes o
    WHERE  o.client_id = c.id
    ORDER  BY o.cree_le DESC
    LIMIT  3
) last3;

Astuce : LATERAL + LIMIT avec un index sur (client_id, cree_le DESC) est l'une des formes les plus efficaces pour un « top N par groupe » quand il y a beaucoup de clients et peu de lignes utiles par client.