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

问题描述

正如标题所暗示的，我想选择每组行集合中的第一行，这些行集合在 GROUP BY 中。

具体而言，如果我有一个购买表，如下所示：

SELECT * FROM purchases;


我的输出：

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

我想查询最大购买的 id 总数）由每个客户进行。例如：
$ b

SELECT FIRST（id），customer，FIRST（total）
FROM购买
GROUP BY客户
ORDER BY total DESC;


预期成果：

 
 FIRST（id）|客户| FIRST（总计）
 ---------- + ---------- + ------------- 
 1 |乔| 5 
 2 |莎莉|在Oracle 9.2+（不是8i +，如最初所述），SQL Server 2005+上，在Oracle 9.2及以上版本中， PostgreSQL 8.4+，DB2，Firebird 3.0+，Teradata，Sybase，Vertica： 
 $ b 
  WITH summary（
 SELECT p .id，
 p.customer，
 p.total，
 ROW_NUMBER（）OVER（分区由p.customer 
 ORDER BY p.total DESC）AS rk 
 FROM PURCHASES p）
 SELECT s。* 
 FROM summary s 
 WHERE s.rk = 1 
  
 
 
 
任何数据库都支持：
 
 
 
但是您需要添加逻辑来打破关系： 
 
 
  SELECT MIN（x.id）， - 如果您想要最高
 x.customer，
 x.total 
 FROM，则更改为MAX购买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 
  
 


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


Specifically, if I've got a purchases table that looks like this:
SELECT * FROM purchases;
My Output:
id | customer | total
---+----------+------
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1
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;
Expected Output:
FIRST(id) | customer | FIRST(total)
----------+----------+-------------
        1 | Joe      | 5
        2 | Sally    | 3

解决方案
On Oracle 9.2+ (not 8i+ as originally stated), SQL Server 2005+, PostgreSQL 8.4+, DB2, Firebird 3.0+, Teradata, Sybase, Vertica:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rk
      FROM PURCHASES p)
SELECT s.*
  FROM summary s
 WHERE s.rk = 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屋！