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 BYprecisa começar com as expressões doDISTINCT ON, senão o PostgreSQL gera "SELECT DISTINCT ON expressions must match initial ORDER BY expressions". - Sem um
ORDER BYque 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.