尋找一年中最高的季度

我坐在這裡做一些練習，但我找不到解決方案。也許有人可以給我一些幫助？

我有一張桌子，應該找到每年一個季度的最高銷售額。

數據表：

我的嘗試：

SELECT Year, Quarter, MAX(SUM(Revenue)) OVER(PARTITION BY Year)
FROM Sale
GROUP BY Year, Quarter
ORDER BY Year, Quarter;

輸出：

如您所見，數字是正確的。

但我的預期輸出應該是：

2012  |  4  |  25
2013  |  2  |  35

如果有人有想法會很棒。

提前致謝！

更新： 找到解決方案。

忘了提。我使用了 Oracle XE。

SELECT Year, Quarter, QMax
FROM (
 SELECT Year, Quarter,
   MAX(SUM(Revenue)) OVER(PARTITION BY Year) AS QMax,
   RANK() OVER (PARTITION BY Year ORDER BY SUM(Revenue) DESC) AS QRank
 FROM Sale
 GROUP BY Year, Quarter) QResult
WHERE QRank = 1
ORDER BY Year, Quarter;

--Start by getting your quarters:
WITH Qs AS
(SELECT Year, Quarter, SUM(Revenue) AS QRev
FROM Sale
GROUP BY Year, Quarter
),
--Now rank them by year
QRanks AS
(SELECT *, RANK() OVER (PARTITION BY Year ORDER BY QRev DESC) as QRank
FROM Qs
)
--Now filter
SELECT Year, Quarter, QRev
FROM QRanks
WHERE QRank = 1
ORDER BY Year;

希望這就是你所追求的。:)

假設答案是 25 和 30，那麼這應該有效

SELECT Year, MAX(Quarter) Quarter, MAX(Revenue)
FROM Sale
GROUP BY Year
ORDER BY Year, Quarter

引用自：https://dba.stackexchange.com/questions/69309