在支持CTE和窗口功能的数据库上：

WITH summary AS (SELECT p.id,p.customer,p.total,ROW_NUMBER() OVER(PARTITION BY p.customerORDER BY p.total DESC) AS rankFROM PURCHASES p)SELECT *FROM summaryWHERE rank = 1

支持任何数据库：

但是你需要添加逻辑来打破联系：

  SELECT MIN(x.id),  -- change to MAX if you want the highestx.customer,x.totalFROM PURCHASES xJOIN (SELECT p.customer,MAX(total) AS max_totalFROM PURCHASES pGROUP BY p.customer) y ON y.customer = x.customerAND y.max_total = x.totalGROUP BY x.customer, x.total

在PostgreSQL中，#0通常是最简单和最快的。
_{（有关某些工作负载的性能优化，请参阅下文。）}

SELECT DISTINCT ON (customer)id, customer, totalFROM   purchasesORDER  BY customer, total DESC, id;

或者更短（如果不清楚），输出列的序数：

SELECT DISTINCT ON (2)id, customer, totalFROM   purchasesORDER  BY 2, 3 DESC, 1;

如果total可以为NULL，则添加NULLS LAST：

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

无论哪种方式都有效，但你会想要匹配现有索引

db<>fiddle这里

主要观点

#0是标准的PostgreSQL扩展，其中仅定义了整个SELECT列表中的DISTINCT。

在DISTINCT ON子句中列出任意数量的表达式，组合的行值定义重复项。手册：

显然，如果两行至少在一个列值。空值在此被视为相等比较。

大胆强调我的。

DISTINCT ON可以与#1组合。ORDER BY中的前导表达式必须在DISTINCT ON中的表达式集中，但您可以自由地重新排列它们之间的顺序。例子。
您可以将额外表达式添加到ORDER BY以从每组对等体中选择特定行。或者，如手册上说：

DISTINCT ON表达式必须匹配最左边的ORDER BY表达式。ORDER BY子句通常包含额外的表达式，用于确定所需的行优先级每个DISTINCT ON组。

我添加了id作为断开关系的最后一项：
"从共享最高total的每个组中选择id最小的行。"

要以与确定每个组的第一个排序顺序不一致的方式对结果进行排序，您可以将上述查询嵌套在外部查询中，并使用另一个ORDER BY.例子。

如果total可以为NULL，则最有可能想要具有最大非空值的行。像演示的那样添加#1。请参阅：

• 按列ASC排序，但首先是NULL值？

#0列表不以任何方式受DISTINCT ON或ORDER BY中的表达式的约束：

• 您不需要包含DISTINCT ON或ORDER BY中的任何表达式。

• 您可以在SELECT列表中包含任何其他表达式。这有助于替换复杂的子查询和聚合/窗口函数。

我使用Postgres 8.3-15版本进行了测试。但该功能至少从7.1版本开始就存在了，所以基本上一直存在。

索引

上述查询的完美索引将是多列索引，以匹配的顺序和匹配的排序顺序跨越所有三列：

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

可能过于专业。但如果特定查询的读取性能至关重要，请使用它。如果查询中有DESC NULLS LAST，请在索引中使用相同的值，以便排序顺序匹配，索引完全适用。

效果/性能优化

在为每个查询创建定制索引之前，权衡成本和收益。上述索引的潜力在很大程度上取决于数据分布。

使用索引是因为它提供预先排序的数据。在Postgres 9.2或更高版本中，如果索引小于基础表，查询也可以从索引扫描中受益。但是，必须完整扫描索引。例子。

对于每个客户几行（列customer中的高基数），这是非常有效的。如果您无论如何都需要排序输出，则更是如此。随着每个客户的行数越来越多，好处会缩小。
理想情况下，你有足够的#0来处理RAM中的相关排序步骤，而不是溢出到磁盘。但通常将work_mem也设置为高会产生不利影响。对于非常大的查询，考虑SET LOCAL。找到你需要EXPLAIN ANALYZE的数量。在排序步骤中提到“磁盘：”表明需要更多：

• PostgreSQL中的配置参数work_memLinux
• 使用ORDER BY日期和文本优化简单查询

对于每个客户多行（第0列中的低基数），“索引跳过扫描”或"松散索引扫描"会更有效率。但直到Postgres 15才实现。多年来，以这种或那种方式实现它的认真工作一直在进行，但迄今为止不成功。见这里和这里。
目前，有更快的查询技术可以代替它。特别是如果您有一个单独的表来保存唯一的客户，这是典型的用例。但是如果您没有：

• SELECT DISTINCT在PostgreSQL表中比预期的慢
• 优化GROUP BY查询以检索每个用户的最新行
• 优化分组最大查询
• 每行查询最后N个相关行

基准

见单独答复。

由于存在SubQ，解决方案并不像Erwin所指出的那样非常有效

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

这是常见的原始文章与许多其他解决方案9;每群最大n个&原始文章与许多其他解决方案9;" aria-label="show questions tagged &原始文章与许多其他解决方案9;每群最大n个&原始文章与许多其他解决方案9;" rel="tag" aria-labelledby="每群最大n个-container">每群最大n个问题，已经有很好的测试和高度优化的解决方案。就我个人而言，我更喜欢左连接解决方案by Bill Karwin（原始文章与许多其他解决方案）。

请注意，这个常见问题的一系列解决方案可以在mysql手册中找到-即使您的问题是在Postgres中，而不是MySQL中，给出的解决方案应该适用于大多数SQL变体。参见常见查询的示例:: 持有某个列的组最大值的行。

非常快速的解决方案

SELECT a.*FROMpurchases aJOIN (SELECT customer, min( id ) as idFROM purchasesGROUP BY customer) b USING ( id );

如果table是由id索引的，那么真的非常快：

create index purchases_id on purchases (id);

在Postgres中，您可以像这样使用array_agg：

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

这将为您提供每个客户最大购买量的id。

有些事情要注意：

• array_agg是一个聚合函数，因此它适用于GROUP BY。
• array_agg允许您指定一个范围仅为其本身的排序，因此它不会限制整个查询的结构。如果您需要执行与默认值不同的操作，还有如何对NULL进行排序的语法。
• 构建数组后，我们获取第一个元素。（Postgres数组是1索引的，而不是0索引的）。
• 您可以以类似的方式将array_agg用于第三个输出列，但max(total)更简单。
• 与DISTINCT ON不同，使用array_agg可以让你保留GROUP BY，以防你出于其他原因想要它。

我使用这种方式（仅限postgresql）：https://wiki.postgresql.org/wiki/First/last_%28aggregate%29

-- Create a function that always returns the first non-NULL itemCREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$SELECT $1;$$;
-- And then wrap an aggregate around itCREATE AGGREGATE public.first (sfunc    = public.first_agg,basetype = anyelement,stype    = anyelement);
-- Create a function that always returns the last non-NULL itemCREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$SELECT $2;$$;
-- And then wrap an aggregate around itCREATE AGGREGATE public.last (sfunc    = public.last_agg,basetype = anyelement,stype    = anyelement);

然后您的示例应该按原样工作几乎：

SELECT FIRST(id), customer, FIRST(total)FROM  purchasesGROUP BY customerORDER BY FIRST(total) DESC;

CAVEAT：它忽略NULL行

编辑1-改用postgres扩展

现在我用这种方式：http://pgxn.org/dist/first_last_agg/

在ubuntu 14.04上安装：

apt-get install postgresql-server-dev-9.3 git build-essential -ygit clone git://github.com/wulczer/first_last_agg.gitcd first_last_appmake && sudo make installpsql -c 'create extension first_last_agg'

这是一个postgres扩展，为您提供第一个和最后一个函数；显然比上面的方式更快。

编辑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 purchasesGROUP BY customerORDER BY first(total);

当然，您可以在聚合中按照您认为合适的方式排序和过滤；它是非常强大的语法。

基准

我测试了最有趣的候选人：

• 最初是员额9.4和9.5。
• 稍后为员额13添加了重音测试。

基本测试设置

主表：purchases：

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

虚拟数据（有一些死元组），PK，索引：

INSERT INTO purchases (customer_id, total, some_column)    -- 200k rowsSELECT (random() * 10000)::int             AS customer_id  -- 10k distinct 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表-用于优化查询：

CREATE TABLE customer ASSELECT customer_id, 'customer_' || customer_id AS customerFROM   purchasesGROUP  BY 1ORDER  BY 1;
ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id);
VACUUM ANALYZE customer;

在我对9.5的第二次测试中，我使用了相同的设置，但使用100000个不同的customer_id来获得每个customer_id的少数行。

表purchases的对象大小

基本设置：purchases中200k行，10k不同的customer_id，平均每个客户20行。
对于Postgres 9.5，我添加了第二个测试，有86446个不同的客户-平均每个客户2.3行。

使用从这里获取的查询生成：

• 测量PostgreSQL表行的大小

订阅关于Postgres 9.5的评论：

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

查询

1. CTE中的row_number()，（见其他答案）

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

2.子查询中的row_number()（我的优化）

SELECT id, customer_id, totalFROM   (SELECT id, customer_id, total, row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rnFROM   purchases) subWHERE  rn = 1;

3.DISTINCT ON（见其他答案）

SELECT DISTINCT ON (customer_id)id, customer_id, totalFROM   purchasesORDER  BY customer_id, total DESC, id;

4.带有LATERAL子查询的rCTE（在这里看到）

WITH RECURSIVE cte AS ((  -- parentheses requiredSELECT id, customer_id, totalFROM   purchasesORDER  BY customer_id, total DESCLIMIT  1)UNION ALLSELECT u.*FROM   cte c,      LATERAL (SELECT id, customer_id, totalFROM   purchasesWHERE  customer_id > c.customer_id  -- lateral referenceORDER  BY customer_id, total DESCLIMIT  1) u)SELECT id, customer_id, totalFROM   cteORDER  BY customer_id;

5.customer表LATERAL（在这里看到）

SELECT l.*FROM   customer c,      LATERAL (SELECT id, customer_id, totalFROM   purchasesWHERE  customer_id = c.customer_id  -- lateral referenceORDER  BY total DESCLIMIT  1) l;

6.array_agg()与ORDER BY（见其他答案）

SELECT (array_agg(id ORDER BY total DESC))[1] AS id, customer_id, max(total) AS totalFROM   purchasesGROUP  BY customer_id;

搜索结果

上述查询的执行时间与#0、最好的5次跑步进行比较，以暖缓存。

所有查询在purchases2_3c_idx上使用仅索引扫描（以及其他步骤）。有些只是为了从较小的索引大小中受益，有些则更有效。

A. Postgres 9.4，200k行，每customer_id~20

1. 273.274 ms2. 194.572 ms3. 111.067 ms4.  92.922 ms  -- !5.  37.679 ms  -- winner6. 189.495 ms

B.与A.相同，Postgres 9.5

1. 288.006 ms2. 223.032 ms3. 107.074 ms4.  78.032 ms  -- !5.  33.944 ms  -- winner6. 211.540 ms

C.与B.相同，但每customer_id有~2.3行

1. 381.573 ms2. 311.976 ms3. 124.074 ms  -- winner4. 710.631 ms5. 311.976 ms6. 421.679 ms

2021-08-11用Postgres 13复试

简化的测试设置：没有删除的行，因为VACUUM ANALYZE完全清理了简单情况下的表。

Postgres的重要变化：

• 一般性能改进。
• 自Postgres 12以来，CTE可以内联，因此查询1.和2.现在执行的大部分相同（相同的查询计划）。

D.每customer_id20行

1. 103 ms2. 103 ms3.  23 ms  -- winner4.  71 ms5.  22 ms  -- winner6.  81 ms

db<>fiddle这里

E.像C.~2.3行每customer_id

1. 127 ms2. 126 ms3.  36 ms  -- winner4. 620 ms5. 145 ms6. 203 ms

db<>fiddle这里

使用Postgres 13进行重音测试

1M排,10.000 vs.100 vs.每个客户1.6行。

F.每个客户约10,000行

1. 526 ms2. 527 ms3. 127 ms4.   2 ms  -- winner !5.   1 ms  -- winner !6. 356 ms

db<>fiddle这里

G.每位客户约100行

1. 535 ms2. 529 ms3. 132 ms4. 108 ms  -- !5.  71 ms  -- winner6. 376 ms

db<>fiddle这里

H.每个客户约1.6行

1.  691 ms2.  684 ms3.  234 ms  -- winner4. 4669 ms5. 1089 ms6. 1264 ms

db<>fiddle这里

结论

• DISTINCT ON有效地使用索引，通常对每组少数行执行最佳。即使每组有很多行，它也能正常执行。

• 对于每个组许多行，使用rCTE模拟索引跳过扫描执行效果最好-仅次于使用单独查找表的查询技术（如果可用）。

• 在当前接受的答案从来没有赢过任何性能测试中展示了#0技术。当时不是，现在不是。它永远不会接近DISTINCT ON，即使数据分布不利于后者。关于row_number()的唯一好处：它的规模并不大，只是平庸。

更多基准

用10M行和60k独特的“客户”对员额11.5进行“ogr”基准测试。结果与我们目前看到的一致：

• 访问每个单独标识符的最新行的正确方法？

2011年的原始（过时）基准

我使用PostgreSQL9.1在一个包含65579行的真实表上运行了三次测试，并在所涉及的三列中的每一列上运行了单列btree索引，并在5次运行中获得了最好的执行时间。
比较@陈志立第一个查询（#0）和以上#1解决方案（#2）：

1. 选择整个表，在本例中会产生5958行。

A: 567.218 msB: 386.673 ms

2. 使用条件WHERE customer BETWEEN x AND y导致1000行。

A: 249.136 msB:  55.111 ms

3. 选择WHERE customer = x的单个客户。

A:   0.143 msB:   0.072 ms

用另一个答案中描述的索引重复相同的测试：

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

1A: 277.953 ms1B: 193.547 ms
2A: 249.796 ms -- special index not used2B:  28.679 ms
3A:   0.120 ms3B:   0.048 ms

从我的测试来看，公认的OMG Ponies的“任何数据库支持”解决方案具有良好的速度。

在这里，我提供了一个相同的方法，但更完整和干净的任何数据库解决方案。连接被考虑（假设希望每个客户只获得一行，甚至每个客户的最大总数有多条记录），其他购买字段（例如purchase_payment_id）将被选择用于购买表中真正匹配的行。

支持任何数据库：

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

这个查询相当快，特别是当购买表上有一个复合索引（客户，总数）时。

备注：

1. t1、t2是子查询别名，可以根据数据库删除。

2. 注意：using (...)子句目前在MS-SQL和Oracle db中不受支持。您必须自己将其扩展为例如on t2.id = purchase.id等。USING语法适用于SQLite、MySQL和PostgreSQL。

查询：

SELECT purchases.*FROM purchasesLEFT JOIN purchases as pONp.customer = purchases.customerANDpurchases.total < p.totalWHERE p.total IS NULL

这是如何工作的！（我去过那里）

我们希望确保每次购买只有最高的总数。

一些理论上的东西（如果您只想了解查询，请跳过此部分）

设Total是一个函数T（客户，id），它返回一个给定名称和id的值为了证明给定的总数（T（客户，id））是最高的，我们必须证明我们想证明

• x T（客户，id）>T（客户，x）（这个总数高于所有其他总客户）

或

• x T（客户，id）

第一种方法需要我们获得我不太喜欢的那个名字的所有记录。

第二个需要一个聪明的方式来说不可能有比这个更高的记录。

回到SQL

如果我们左连接表的名称和总数小于连接表：

LEFT JOIN purchases as pONp.customer = purchases.customerANDpurchases.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 |      |        |         |+--------------+----------------+-----------------+------+--------+---------+

这就是我们需要的答案。

• 如果要从聚合行集中选择任何（根据特定条件）行。

• 如果您想在max/min之外使用另一个（sum/avg）聚合函数。因此您不能对DISTINCT ON使用线索

您可以使用下一个子查询：

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

您可以使用一个限制将amount = MAX( tf.amount )替换为您想要的任何条件：此子查询不得返回多行

但如果你想做这样的事情，你可能会寻找窗口函数

在SQL服务器中，您可以这样做：

SELECT *FROM (SELECT ROW_NUMBER()OVER(PARTITION BY customerORDER BY total DESC) AS StRank, *FROM Purchases) nWHERE StRank = 1

解释：这里集团按是在客户的基础上完成的，然后按总数订购，然后每个这样的组都被赋予序列号为StRank，我们正在淘汰第一个StRank为1的客户

对于SQl Server，最有效的方法是：

withids as ( --condition for split table into groupsselect i from (values (9),(12),(17),(18),(19),(20),(22),(21),(23),(10)) as v(i)),src as (select * from yourTable where  <condition> --use this as filter for other conditions),joined as (select tops.* from idscross apply --it`s like for each rows(select top(1) *from srcwhere CommodityId = ids.i) as tops)select * from joined

不要忘记为使用过的列创建聚集索引

对PostgreSQL、U-SQL、IBMDB2和Google BigQuerySQL使用ARRAY_AGG函数：

SELECT customer, (ARRAY_AGG(id ORDER BY total DESC))[1], MAX(total)FROM purchasesGROUP BY customer

Snowflake/Teradata支持#0子句，其工作方式类似于窗口函数的HAVING：

SELECT id, customer, totalFROM PURCHASESQUALIFY ROW_NUMBER() OVER(PARTITION BY p.customer ORDER BY p.total DESC) = 1

在PostgreSQL中，另一种可能性是结合SELECT DISTINCT使用#0窗口函数：

select distinct customer_id,first_value(row(id, total)) over(partition by customer_id order by total desc, id)from            purchases;

我创建了一个复合(id, total)，因此两个值都由相同的聚合返回。您当然可以始终应用first_value()两次。

这对我来说是有效的：

SELECT article, dealer, priceFROM   shop s1WHERE  price=(SELECT MAX(s2.price)FROM shop s2WHERE s1.article = s2.articleGROUP BY s2.article)ORDER BY article;

选择每件商品的最高价

我的方法通过窗口函数dbfiddle：

1. 在每个组分配row_number：row_number() over (partition by agreement_id, order_id ) as nrow
2. 在组中只取第一行：filter (where nrow = 1)

with intermediate as (select*,row_number() over ( partition by agreement_id, order_id ) as nrow,(sum( suma ) over ( partition by agreement_id, order_id ))::numeric( 10, 2) as order_suma,from <your table>)
select*,sum( order_suma ) filter (where nrow = 1) over (partition by agreement_id)from intermediate

这可以很容易地实现MAX函数在总和GROUP BY ID和客户。

SELECT id, customer, MAX(total) FROM  purchases GROUP BY id, customerORDER BY total DESC;

这就是我们如何通过使用windows函数来实现这一点：

    create table purchases (id int4, customer varchar(10), total integer);insert into purchases values (1, 'Joe', 5);insert into purchases values (2, 'Sally', 3);insert into purchases values (3, 'Joe', 2);insert into purchases values (4, 'Sally', 1);    
select ID, CUSTOMER, TOTAL from (select ID, CUSTOMER, TOTAL,row_number () over (partition by CUSTOMER order by TOTAL desc) RNfrom purchases) A where RN = 1;