在每个 GROUP BY 组中选择第一行?

Select first row in each GROUP BY group?(在每个 GROUP BY 组中选择第一行?)

问题描述

正如标题所示，我想选择用 GROUP BY 分组的每组行的第一行.

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY.

具体来说，如果我有一个如下所示的 purchases 表:

Specifically, if I've got a purchases table that looks like this:

SELECT * FROM purchases;

我的输出:

我想查询每个客户的最大购买量(total)的id.像这样的:

I'd like to query for the id of the largest purchase (total) made by each customer. Something like this:

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

预期输出:

推荐答案

在 支持CTE和窗口函数:

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

任何数据库都支持:

但你需要添加逻辑来打破关系:

Supported by any database:

But you need to add logic to break ties:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total

这篇关于在每个 GROUP BY 组中选择第一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！