В PostgreSQL вы можете использовать array_agg следующим образом:

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

Это вернет id самой крупной покупки каждого клиента.

Некоторые моменты, на которые стоит обратить внимание:

• array_agg — это агрегатная функция, поэтому она работает в сочетании с GROUP BY.
• array_agg позволяет вам задавать порядок, применимый только к самому агрегату, так что это не ограничивает структуру всего запроса. Также есть синтаксис для сортировки NULL, если вам нужно что-то отличное от значения по умолчанию.
• После того как массив построен, мы берем первый элемент. (Массивы в PostgreSQL индексируются с 1, а не с 0).
• Вы также можете использовать array_agg аналогичным образом для третьего столбца в выводе, но использование max(total) проще.
• В отличие от DISTINCT ON, использование array_agg позволяет сохранить ваш GROUP BY, на случай если он понадобится вам по другим причинам.

Запрос:

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

КАК ЭТО РАБОТАЕТ! (Я это проходил)

Мы хотим удостовериться, что у нас есть только самые большие суммы для каждой покупки.

Некоторые теоретические моменты (пропустите эту часть, если хотите только понять запрос)

Пусть Total - это функция T(customer,id), которая возвращает значение, заданное именем и ID. Чтобы доказать, что заданная сумма (T(customer,id)) является самой высокой, нам нужно доказать, что:

Мы хотим доказать либо

• ∀x T(customer,id) > T(customer,x) (эта сумма выше всех других сумм для данного клиента)

ИЛИ

• ¬∃x T(customer, id) < T(customer, x) (существует ли более высокая сумма для данного клиента)

Первый подход потребует от нас получить все записи для данного имени, что мне не очень нравится.

Второй же способ потребует умного решения, чтобы сказать, что не может быть записи с более высокой суммой, чем эта.

Вернемся к SQL

Если мы делаем левое соединение таблицы по имени и сумме, которая меньше, чем та, которая соединяется:

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

мы уверяемся в том, что все записи, имеющие другую запись с более высокой суммой для того же пользователя, будут соединены:

+--------------+---------------------+-----------------+------+------------+---------+
| 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 |      |            |         |
+--------------+---------------------+-----------------+------+------------+---------+

Это поможет нам отфильтровать самые высокие суммы для каждой покупки, без необходимости группировки:

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 |      |        |         |
+--------------+----------------+-----------------+------+--------+---------+

И это именно то, что нам нужно.

Решение не очень эффективное, как отметил Эрвин, из-за наличия подзапросов.

SELECT * FROM purchases p1 
WHERE total IN (
    SELECT MAX(total) 
    FROM purchases 
    WHERE p1.customer = customer
) 
ORDER BY total DESC;

Проблема здесь заключается в том, что подзапрос выполняется для каждой строки в основной таблице purchases, что может существенно замедлить выполнение запроса, особенно при большом объёме данных.

Вместо этого можно рассмотреть вариант использования JOIN для повышения производительности. Например:

SELECT p1.* 
FROM purchases p1
JOIN (
    SELECT customer, MAX(total) AS max_total 
    FROM purchases 
    GROUP BY customer
) p2 ON p1.customer = p2.customer AND p1.total = p2.max_total 
ORDER BY p1.total DESC;

Такой подход позволяет избежать повторного выполнения подзапроса и значительно улучшить производительность запроса.

Я использую следующий способ (только для PostgreSQL): https://wiki.postgresql.org/wiki/First/last_%28aggregate%29

-- Создаем функцию, которая всегда возвращает первый непустой элемент
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- Затем оборачиваем его в агрегат
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Создаем функцию, которая всегда возвращает последний непустой элемент
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- Затем оборачиваем его в агрегат
CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

После этого ваш пример должен работать почти так, как есть:

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

ПРИМЕЧАНИЕ: Он игнорирует строки с NULL

Редактирование 1 - Используйте расширение PostgreSQL вместо этого

Теперь я использую следующий способ: http://pgxn.org/dist/first_last_agg/

Для установки на 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_agg
make && sudo make install
psql -c 'create extension first_last_agg'

Это расширение для PostgreSQL, которое предоставляет функции first и last; похоже, работает быстрее, чем описанный выше способ.

Редактирование 2 - Упорядочение и фильтрация

Если вы используете агрегатные функции (как эти), вы можете упорядочить результаты без необходимости предварительно упорядочивать данные:

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

Так что эквивалентный пример с упорядочиванием будет выглядеть так:

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

Конечно, вы можете упорядочивать и фильтровать по своему усмотрению внутри агрегатов; это очень мощный синтаксис.

Ответ очень быстрый

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

Этот запрос будет действительно очень быстрым, если таблица индексирована по полю id:

CREATE INDEX purchases_id ON purchases (id);