选择每个 GROUP BY 组中的第一行? [英] Select first row in each GROUP BY group?

问题描述

正如标题所暗示的，我想选择用 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;

我的输出:

<头>

id	客户	总计
1	乔	5
2	莎莉	3
3	乔	2
4	莎莉	1

我想查询每个客户的最大购买量(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;

预期输出:

<头>

FIRST(id)	客户	第一个(总计)
1	乔	5
2	莎莉	3

推荐答案

关于支持 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 组中的第一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！