仅显示具有最大计数值的行

发布于 2024-12-03 20:52:31 字数 453 浏览 2 评论 0原文

我试图从以下查询中仅提取最大计数的行：

SELECT FOO, BAR, COUNT(X)
FROM TABLE1, TABLE2, TABLE3
WHERE <CONDITIONS INVOLVING THE 3 TABLES>
GROUP BY FOO, BAR;

这会生成如下所示的输出 -

FOO |  BAR        | COUNT(X)
 0     String 1       5
 1     String 2       3
 2     String 3       5
 3     String 4       4
 4     String 5       5

如您所见，有 3 行的 COUNT(X) 值为 5。我如何将其编辑为只显示包含 MAX(COUNT(X)) 的行？我正在使用 Oracle SQL。

原文

I'm trying to extract only the rows with the maximum count from the following query:

SELECT FOO, BAR, COUNT(X)
FROM TABLE1, TABLE2, TABLE3
WHERE <CONDITIONS INVOLVING THE 3 TABLES>
GROUP BY FOO, BAR;

Which generates output that looks like -

FOO |  BAR        | COUNT(X)
 0     String 1       5
 1     String 2       3
 2     String 3       5
 3     String 4       4
 4     String 5       5

As you can see, there are 3 rows with a COUNT(X) value of 5. How would I edit this to ONLY display the rows that contain MAX(COUNT(X))? I am using Oracle SQL.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

暗恋未遂 2024-12-10 20:52:31

您可以使用分析函数来做到这一点。首先找到每组值的计数，就像您已经做的那样；然后使用 DENSE_RANK() （或 RANK() 或 ROW_NUMBER()) 函数为现有结果中的每一行分配顺序根据 COUNT() 值设置基础；最后只选择排名最高的：

SELECT foo, bar, cnt
FROM (
    SELECT foo, bar, cnt,
        DENSE_RANK() OVER (ORDER BY cnt DESC) dr
    FROM(
        SELECT foo, bar, COUNT(x) cnt
        FROM ...
        GROUP BY foo, bar
    )
)
WHERE dr = 1;

注意 ORDER BY 中的 DESC，因此最高的 COUNT() 被分配给排名1。

You can do this with analytic functions. First find the count as for each set of values, as you are already; then use a DENSE_RANK() (or RANK(), or ROW_NUMBER()) function to assign an order to each rows in your existing result set base on the COUNT() value; and finally only pick the ones with the highest rank:

SELECT foo, bar, cnt
FROM (
    SELECT foo, bar, cnt,
        DENSE_RANK() OVER (ORDER BY cnt DESC) dr
    FROM(
        SELECT foo, bar, COUNT(x) cnt
        FROM ...
        GROUP BY foo, bar
    )
)
WHERE dr = 1;

Note the DESC in the the ORDER BY, so the highest COUNT() is assigned the rank of 1.

回复收藏 0 原文

狼亦尘 2024-12-10 20:52:31

这应该有效：

SELECT * FROM (
SELECT FOO, BAR, MAX(X) maxX, COUNT(X) cntX
FROM TABLE1, TABLE2, TABLE3
WHERE <CONDITIONS INVOLVING THE 3 TABLES>
GROUP BY FOO, BAR
) A
WHERE maxX = cntX;

This should work:

SELECT * FROM (
SELECT FOO, BAR, MAX(X) maxX, COUNT(X) cntX
FROM TABLE1, TABLE2, TABLE3
WHERE <CONDITIONS INVOLVING THE 3 TABLES>
GROUP BY FOO, BAR
) A
WHERE maxX = cntX;

回复收藏 0 原文

~没有更多了~