In PostgreSQL this is typically simpler and faster (more performance optimization below):

SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;

Ou mais curto (se não tão claro) com números ordinais de colunas de saída:

SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;

Se total pode ser nulo (não vai doer de nenhuma maneira, mas vai querer corresponder aos índices existentes):

...
ORDER  BY customer, total DESC NULLS LAST, id;

Pontos principais

• DISTINCT ON é uma extensão PostgreSQL da norma (onde apenas DISTINCT em toda a lista SELECT está definido).

• Enumerar qualquer número de expressões na Cláusula DISTINCT ON, o valor combinado da linha define duplicados. o manual:

  Obviamente, duas linhas são consideradas distintas se diferirem pelo menos em um valor de coluna. Os valores nulos são considerados iguais nesta comparação.

  Ênfase ousada minha.

• DISTINCT ON pode ser combinado com ORDER BY. As expressões iniciais têm de corresponder ao 'leading' DISTINCT ON expressões na mesma ordem. Você pode adicionarexpressões adicionais a ORDER BY para escolher uma dada linha de cada grupo de pares. Eu adicionei id como último item para quebrar laços:

  "escolha a linha com o menor id de cada grupo que partilha o mais alto total."

  Se total pode ser nulo, você provavelmente quer a linha com o maior valor não nulo. Adicionar NULLS LAST como demonstrado. Detalhes:

  • PostgreSQL Ordenar por datetime asc, null primeiro?

• A lista SELECT não é restringida por expressões em DISTINCT ON ou ORDER BY de qualquer forma. (Não necessário no caso simples acima):

  • Você não tem que incluir nenhuma das expressões em DISTINCT ON ou ORDER BY.

  • Você pode incluir qualquer outra expressão na lista SELECT. Isto é fundamental para substituir consultas muito mais complexas por subquerias e agregado / janela funcao.

• Eu testei com versões Postgres 8.3-10. Mas o recurso tem estado lá pelo menos desde a versão 7.1, então basicamente sempre.

Índice

O índice perfeito para a consulta acima seria um índice multi-coluna abrangendo todas as três colunas em sequência correspondente e com ordem de ordenação correspondente:

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

Eficácia / optimização do desempenho

Parâmetro de referência

Teste o mais interessante candidatos com Postgres 9.4 e 9.5 com um a meio caminho de mesa realistas de 200k linhas em purchases e 10k distintas customer_id (avg. 20 linhas por cliente ).

Configuração

Tabela principal

CREATE TABLE purchases (
  id          serial
, customer_id int  -- REFERENCES customer
, total       int  -- could be amount of money in Cent
, some_column text -- to make the row bigger, more realistic
);

Uso um serial (PK restrição adicionada abaixo) e um inteiro customer_id, uma vez que é uma configuração mais típica. Também adicionou some_column para compensar tipicamente mais colunas.

Dados fictícios, PK, index - uma tabela típica também tem algumas tuplas mortas:

INSERT INTO purchases (customer_id, total, some_column)    -- insert 200k rows
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,200000) g;

ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id);

DELETE FROM purchases WHERE random() > 0.9; -- some dead rows

INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,20000) g;  -- add 20k to make it ~ 200k

CREATE INDEX purchases_3c_idx ON purchases (customer_id, total DESC, id);

VACUUM ANALYZE purchases;

customer tabela-para pesquisa superior

CREATE TABLE customer AS
SELECT customer_id, 'customer_' || customer_id AS customer
FROM   purchases
GROUP  BY 1
ORDER  BY 1;

ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id);

VACUUM ANALYZE customer;

No meu segundo teste para a rede de 9,5 eu usei a mesma configuração, mas com random() * 100000 para gerar customer_id para obter apenas poucas linhas por customer_id.

Tamanhos de objectos para a tabela purchases

Gerado com esta consulta .

               what                | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 20496384 | 20 MB        |           102
 visibility_map                    |        0 | 0 bytes      |             0
 free_space_map                    |    24576 | 24 kB        |             0
 table_size_incl_toast             | 20529152 | 20 MB        |           102
 indexes_size                      | 10977280 | 10 MB        |            54
 total_size_incl_toast_and_indexes | 31506432 | 30 MB        |           157
 live_rows_in_text_representation  | 13729802 | 13 MB        |            68
 ------------------------------    |          |              |
 row_count                         |   200045 |              |
 live_tuples                       |   200045 |              |
 dead_tuples                       |    19955 |              |

Perguntas

1. {[28] } em ETI, (ver outra resposta)

WITH cte AS (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   )
SELECT id, customer_id, total
FROM   cte
WHERE  rn = 1;

2. row_number() em subcontingente (minha optimização)

SELECT id, customer_id, total
FROM   (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   ) sub
WHERE  rn = 1;

3. DISTINCT ON (ver outra resposta)

SELECT DISTINCT ON (customer_id)
       id, customer_id, total
FROM   purchases
ORDER  BY customer_id, total DESC, id;

4. rCTE com LATERAL subquery (veja aqui)

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT id, customer_id, total
   FROM   purchases
   ORDER  BY customer_id, total DESC
   LIMIT  1
   )
   UNION ALL
   SELECT u.*
   FROM   cte c
   ,      LATERAL (
      SELECT id, customer_id, total
      FROM   purchases
      WHERE  customer_id > c.customer_id  -- lateral reference
      ORDER  BY customer_id, total DESC
      LIMIT  1
      ) u
   )
SELECT id, customer_id, total
FROM   cte
ORDER  BY customer_id;

5. customer tabela com LATERAL (veja aqui)

SELECT l.*
FROM   customer c
,      LATERAL (
   SELECT id, customer_id, total
   FROM   purchases
   WHERE  customer_id = c.customer_id  -- lateral reference
   ORDER  BY total DESC
   LIMIT  1
   ) l;

6. array_agg() com ORDER BY (ver outros resposta)

SELECT (array_agg(id ORDER BY total DESC))[1] AS id
     , customer_id
     , max(total) AS total
FROM   purchases
GROUP  BY customer_id;

Resultados

Tempo de execução para consultas acima com EXPLAIN ANALYZE (e todas as opções desligadas), best of 5 runs .

todas as consultas utilizaram um índice apenas digitaliza em purchases2_3c_idx (entre outros passos). Alguns deles apenas para o tamanho menor do Índice, outros mais eficazmente.

A. Postgres 9.4 com linhas de 200k e ~ 20 por customer_id

1. 273.274 ms  
2. 194.572 ms  
3. 111.067 ms  
4.  92.922 ms  
5.  37.679 ms  -- winner
6. 189.495 ms

B. O mesmo acontece com os Postgres 9, 5

1. 288.006 ms
2. 223.032 ms  
3. 107.074 ms  
4.  78.032 ms  
5.  33.944 ms  -- winner
6. 211.540 ms  

C. igual a B., mas com ~ 2.3 linhas por customer_id

1. 381.573 ms
2. 311.976 ms
3. 124.074 ms  -- winner
4. 710.631 ms
5. 311.976 ms
6. 421.679 ms

Referência Original (desactualizada) de 2011

1. Seleccionar a tabela inteira, resulta em 5958 linhas neste caso.

   A: 567.218 ms
   B: 386.673 ms
   

2. Condição de Utilização WHERE customer BETWEEN x AND y resultando em 1000 linhas.

   A: 249.136 ms
   B:  55.111 ms
   

3. Seleccione um único cliente com WHERE customer = x.

   A:   0.143 ms
   B:   0.072 ms
   

O mesmo teste repetido com o índice descrito na outra resposta

CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

1A: 277.953 ms  
1B: 193.547 ms

2A: 249.796 ms -- special index not used  
2B:  28.679 ms

3A:   0.120 ms  
3B:   0.048 ms

Este é um problema comum maior-n-Por-Grupo, que já tem soluções bem testadas e altamente optimizadas . Pessoalmente, prefiro a solução de junta esquerda de Bill Karwin (O post original de com muitas outras soluções ).

Note que um monte de soluções para este problema comum pode ser surpreendentemente encontrado na uma das fontes mais oficiais, MySQL manual! Ver exemplos de consultas comuns:: as linhas que seguram o Grupo Máximo de uma determinada coluna.

Em Postgres pode usar array_agg assim:

SELECT  customer,
        (array_agg(id ORDER BY total DESC))[1],
        max(total)
FROM purchases
GROUP BY customer

Algumas coisas a notar:

• array_agg é uma função agregada, por isso funciona com GROUP BY.
• array_agg permite-lhe especificar uma ordenação delimitada a si mesma, para que não restrinja a estrutura de toda a consulta. Há também uma sintaxe para como você ordenar NULLs, se você precisar fazer algo diferente do padrão.
Uma vez construímos a matriz, tomamos o primeiro elemento. (As matrizes Postgres são indexadas a 1, não indexadas a 0).
• Você poderia usar array_agg de uma forma semelhante para a sua terceira coluna de saída, mas max(total) é mais simples.
• Ao contrário de DISTINCT ON, o uso de array_agg permite-lhe manter o seu GROUP BY, no caso de querer isso por outras razões.

A solução não é muito eficiente como apontado por Erwin, devido à presença de SubQs

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;

Eu uso este caminho (apenas postgresql): https://wiki.postgresql.org/wiki/First/last_%28aggregate%29

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

Então o seu exemplo deve funcionar quase Como é:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY FIRST(total) DESC;

Ressalva: ignora as linhas nulas

Editar 1-Use a extensão postgres em vez de

Agora uso este caminho: http://pgxn.org/dist/first_last_agg/

Para instalar no ubuntu 14.04:

apt-get install postgresql-server-dev-9.3 git build-essential -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && sudo make install
psql -c 'create extension first_last_agg'

Editar 2-ordenação e filtragem

Se você usar funções agregadas( como estas), você pode encomendar os resultados, sem a necessidade de ter os dados já ordenados:

http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

Então o exemplo equivalente, com a ordenação seria algo como:

SELECT first(id order by id), customer, first(total order by id)
  FROM purchases
 GROUP BY customer
 ORDER BY first(total);

Claro que você pode pedir e filtrar como você considera caber dentro do agregado; é uma sintaxe muito poderosa.

Solução muito rápida

SELECT a.* 
FROM
    purchases a 
    JOIN ( 
        SELECT customer, min( id ) as id 
        FROM purchases 
        GROUP BY customer 
    ) b USING ( id );

E muito rapidamente se a tabela for indexada por id:

create index purchases_id on purchases (id);

A Pergunta:

SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p 
ON 
  p.customer = purchases.customer 
  AND 
  purchases.total < p.total
WHERE p.total IS NULL

COMO É QUE ISSO FUNCIONA? (Eu estive lá)

Algumas coisas teóricas (ignorar esta parte se só quiser compreender a Pesquisa)

Deixe o Total ser uma função T (cliente, id) onde devolve um valor dado o nome e o id Para provar que o total indicado (t (cliente, id)) é o mais elevado que temos de provar que Queremos provar:

• ÿx (cliente, id) > T (cliente, x) (Este total é superior a todos os outros total para esse cliente)

Ou

• x x t(cliente, id)

A primeira abordagem vai precisar de nós para obter todos os registros para esse nome que eu realmente não gosto.

Voltar para SQL

Se nos juntarmos à mesa sobre o nome e o total ser inferior à tabela junta:

      LEFT JOIN purchases as p 
      ON 
      p.customer = purchases.customer 
      AND 
      purchases.total < p.total

Garantimos que todos os registos que tenham outro registo com o total mais elevado para o mesmo Utilizador a ser ligado:

purchases.id, purchases.customer, purchases.total, p.id, p.customer, p.total
1           , Tom           , 200             , 2   , Tom   , 300
2           , Tom           , 300
3           , Bob           , 400             , 4   , Bob   , 500
4           , Bob           , 500
5           , Alice         , 600             , 6   , Alice   , 700
6           , Alice         , 700

Isso vai ajudar-nos a filtrar o total mais elevado para cada compra sem necessidade de agrupamento:

WHERE p.total IS NULL

purchases.id, purchases.name, purchases.total, p.id, p.name, p.total
2           , Tom           , 300
4           , Bob           , 500
6           , Alice         , 700

A solução aceite de póneis OMG "suportada por qualquer base de dados" tem boa velocidade do meu teste.

Suportado por qualquer base de dados:

select * from purchase
join (
    select min(id) as id from purchase
    join (
        select customer, max(total) as total from purchase
        group by customer
    ) t1 using (customer, total)
    group by customer
) t2 using (id)
order by customer

Esta consulta é razoavelmente rápida, especialmente quando há um índice composto como (cliente, total) na tabela de compra.

Observação:

1. T1, t2 são alcunhas subquery que podem ser removidas dependendo da base de dados.

2. Caveat : a cláusula using (...) actualmente não é suportada em MS-SQL e Oracle db a partir desta edição em Jan 2017. Você mesmo tem que expandi-lo para, por exemplo, on t2.id = purchase.id etc. A sintaxe usada funciona em SQLite, MySQL e PostgreSQL.

• Se você quiser selecionar qualquer linha (por sua condição específica) do conjunto de Linhas agregadas.

• Se quiser usar outra função de agregação (sum/avg Para além de max/min. Assim você não pode usar o clue com DISTINCT ON

Pode usar o seguinte subquery:

SELECT  
    (  
       SELECT **id** FROM t2   
       WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount )   
    ) id,  
    name,   
    MAX(amount) ma,  
    SUM( ratio )  
FROM t2  tf  
GROUP BY name

Você pode substituir amount = MAX( tf.amount ) por qualquer condição que deseje com uma restrição: esta subquota não deve devolver mais do que uma linha