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

具体来说,如果我有一个如下所示的采购表:

SELECT * FROM purchases;

我的输出:

id customer total
1 Joe 5
2 Sally 3
3 Joe 2
4 Sally 1

我想查询每个客户的最大购买量(总购买量)。类似于:

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

预期输出:

FIRST(id) customer FIRST(total)
1 Joe 5
2 Sally 3

当前回答

这可以通过MAX FUNCTION on total和GROUP by id和customer轻松实现。

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

其他回答

这是一个常见的最大的每组问题,它已经有了经过充分测试和高度优化的解决方案。就我个人而言,我更喜欢比尔·卡尔温(Bill Karwin)的左联解决方案(原始帖子中有很多其他解决方案)。

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

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

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- And then wrap an aggregate around it
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;

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 -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && sudo make install
psql -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 purchases
 GROUP BY customer
 ORDER BY first(total);

当然,您可以根据您认为合适的情况在聚合中进行排序和过滤;这是非常强大的语法。

在PostgreSQL中,另一种可能是将first_value窗口函数与SELECT DISTINCT结合使用:

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()两次。

在SQL Server中,可以执行以下操作:

SELECT *
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY customer
ORDER BY total DESC) AS StRank, *
FROM Purchases) n
WHERE StRank = 1

解释:这里,分组依据是根据客户进行的,然后按总数进行排序,然后给每个这样的组指定序列号为StRank,我们将选出第一个StRank为1的客户

这样对我来说很有效:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article
              GROUP BY s2.article)
ORDER BY article;

选择每篇文章的最高价格