根據最高值對數據進行分組檢查

我有這種形式的數據：

CallNum Value               Accepted
--------------------------------------
971374  81.482204444473609  True
971374  83.783551111089764  False
971374  97.936875555547886  False
971374  97.936875555547886  False
971374  77.037724444409832  False
971374  83.783551111118868  False
971375  102.35080888887751  False
971375  99.664653333340539  False
971375  101.41519999998855  True
971375  98.415355555567658  False
971375  90.222848888864974  False
971375  100.160346666642    False
971375  103.59455111110583  False
971375  90.222848888864974  False
971333  74.722782222175738  False
971333  95.24945333332289   False
971333  98.315848888887558  False
971333  95.24945333332289   False
971333  98.315848888887558  False
971381  93.169031111086952  False
971381  78.444248888874426  False
971381  86.4358755555586    False
971381  101.53599999999278  True
971381  86.438653333345428  False
971381  80.975364444457227  False
971383  86.3230488889094    False
971383  86.3230488888803    False
971383  86.3230488888803    True
971383  86.3230488889094    False
971385  33.150697777775349  False
971385  33.150697777775349  False
971385  27.780991111125331  False
971385  27.780991111125331  True
971333  98.315848888887558  False
971333  54.750293333345326  False
971333  98.315848888887558  False
971333  95.24945333332289   False
971333  95.24945333332289   False
971390  101.3407733333006   False
971390  95.90560444444418   False
971390  85.444066666648723  False
971390  95.908382222231012  False
971390  101.3407733333006   True
971390  85.444066666648723  False

使用者每次呼叫都送出一個請求，獲得多個報價，每個報價都有一個值，並且通常會接受其中一個（並非總是如此）。

我想知道，在接受報價的請求中，哪些請求接受了最高價值的報價，哪些沒有。

如何從這些數據中獲取這些資訊？

---

我嘗試了各種分組，但似乎無法正確處理。

這應該是您正在尋找的。如果不是，您可以用預期輸出的範例更新問題嗎？謝謝。如果您之前沒有使用過 CTE，請注意 CTE 之前的語句需要以分號結束。

DECLARE @calldata TABLE (
   CallNum INT,
   value NUMERIC(25,20),
   Accepted CHAR(5)
);

INSERT INTO @calldata VALUES
(971374,81.482204444473609,'True'),
(971374,83.783551111089764,'False'),
(971374,97.936875555547886,'False'),
(971374,97.936875555547886,'False'),
(971374,77.037724444409832,'False'),
(971374,83.783551111118868,'False'),
(971375,102.35080888887751,'False'),
(971375,99.664653333340539,'False'),
(971375,101.41519999998855,'True'),
(971375,98.415355555567658,'False'),
(971375,90.222848888864974,'False'),
(971375,100.160346666642,'False'),
(971375,103.59455111110583,'False'),
(971375,90.222848888864974,'False'),
(971333,74.722782222175738,'False'),
(971333,95.24945333332289,'False'),
(971333,98.315848888887558,'False'),
(971333,95.24945333332289,'False'),
(971333,98.315848888887558,'False'),
(971381,93.169031111086952,'False'),
(971381,78.444248888874426,'False'),
(971381,86.4358755555586,'False'),
(971381,101.53599999999278,'True'),
(971381,86.438653333345428,'False'),
(971381,80.975364444457227,'False'),
(971383,86.3230488889094,'False'),
(971383,86.3230488888803,'False'),
(971383,86.3230488888803,'True'),
(971383,86.3230488889094,'False'),
(971385,33.150697777775349,'False'),
(971385,33.150697777775349,'False'),
(971385,27.780991111125331,'False'),
(971385,27.780991111125331,'True'),
(971333,98.315848888887558,'False'),
(971333,54.750293333345326,'False'),
(971333,98.315848888887558,'False'),
(971333,95.24945333332289,'False'),
(971333,95.24945333332289,'False'),
(971390,101.3407733333006,'False'),
(971390,95.90560444444418,'False'),
(971390,85.444066666648723,'False'),
(971390,95.908382222231012,'False'),
(971390,101.3407733333006,'True'),
(971390,85.444066666648723,'False');

WITH maxCallNum AS (
SELECT CallNum, value, Accepted, rowNum = ROW_NUMBER() OVER (PARTITION BY CallNum ORDER BY value DESC) FROM @calldata c1
WHERE EXISTS (SELECT 1 FROM @calldata c2 WHERE c1.CallNum = c2.CallNum
   AND Accepted = 'True')
)
SELECT CallNum, value, Accepted
FROM maxCallNum
WHERE rowNum = 1;

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