Menu

DISTINCT ON no PostgreSQL: uma linha por grupo

SELECT DISTINCT ON (key) ... ORDER BY key, sort fica com a primeira linha de cada grupo, como o último pedido de cada cliente. Só existe no PostgreSQL.

Esta página tem editores executáveis. Edite, execute e veja a saída na hora.

SELECT DISTINCT ON (expr) ... retorna uma linha para cada valor distinto de expr: a primeira linha daquele grupo na ordem do ORDER BY. Ordene primeiro pela chave do grupo e depois pelo que decide qual é a "primeira". Para o último pedido de cada cliente, isso é ORDER BY customer, created_at DESC.

A consulta retorna três linhas: o pedido 2 para asha, o 4 para ben e o 5 para chen, o mais recente de cada cliente. DISTINCT ON é uma extensão do PostgreSQL. Outros bancos de dados precisam da forma com funções de janela mostrada mais abaixo.

Como o ORDER BY decide qual linha fica

O PostgreSQL ordena as linhas e fica com a primeira toda vez que o valor do DISTINCT ON muda. Então:

  • O ORDER BY precisa começar com as expressões do DISTINCT ON, senão o PostgreSQL gera "SELECT DISTINCT ON expressions must match initial ORDER BY expressions".
  • Sem um ORDER BY que vá além dessas expressões, a linha mantida é imprevisível e pode mudar entre execuções.

Troque created_at DESC por total DESC e a mesma consulta retorna o maior pedido de cada cliente:

Agora a linha de asha é o pedido 1 (40) e a de ben é o pedido 3 (60).

Desempatar de propósito

Quando duas linhas empatam na coluna de ordenação, qualquer uma pode ganhar. Adicione uma última chave de ordenação que seja única, como a chave primária, para o resultado ser sempre o mesmo:

As linhas 2 e 3 têm 95 pontos. id como última chave faz a linha 2 ganhar.

Agrupar por mais de uma coluna

Liste várias expressões para ter uma linha por combinação:

Cada par de sensor e tipo fica com a leitura mais recente: s1 humidity 40, s1 temp 22, s2 temp 19.

A versão portável com ROW_NUMBER

ROW_NUMBER() numera as linhas dentro de cada partição. Manter o número 1 dá o mesmo resultado do DISTINCT ON, e funciona também no MySQL 8, no SQL Server e no 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;

Use essa forma quando precisar das n primeiras por grupo (rn <= 3) ou quando a consulta tiver que rodar em outro banco de dados. A página de funções de janela explica o PARTITION BY.

Deixar rápido

DISTINCT ON ordena a entrada, então numa tabela grande crie um índice que bata com o ORDER BY:

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

Assim o PostgreSQL pode ler as linhas já em ordem em vez de ordená-las. Com poucos grupos e muitas linhas por grupo, um join LATERAL que pega LIMIT 1 por cliente do mesmo índice costuma ler menos linhas. A página de joins mostra o LATERAL. Compare as duas opções com EXPLAIN ANALYZE nos seus dados.

Perguntas frequentes

Qual a diferença entre DISTINCT e DISTINCT ON?

DISTINCT remove linhas idênticas em todas as colunas selecionadas. DISTINCT ON (a) mantém uma linha para cada valor de a, e as outras colunas vêm da linha que fica em primeiro na ordenação daquele grupo.

DISTINCT ON é SQL padrão?

Não. É uma extensão do PostgreSQL, então não funciona no MySQL nem no SQL Server. A versão portável é ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) numa subconsulta, filtrando a linha número 1.

Por que aparece "SELECT DISTINCT ON expressions must match initial ORDER BY expressions"?

O ORDER BY precisa começar com as mesmas expressões do DISTINCT ON, na mesma ordem. Escreva DISTINCT ON (customer) ... ORDER BY customer, created_at DESC, e não só ORDER BY created_at DESC.

O DISTINCT ON consegue retornar as 3 primeiras linhas por grupo?

Não, ele retorna exatamente uma linha por grupo. Para as n primeiras, numere as linhas com ROW_NUMBER() e mantenha rn <= 3, ou use uma subconsulta LATERAL com LIMIT 3.

Ilustração das linguagens de programação do Coddy

Aprenda a programar com o Coddy

Começar