Grouping data checking against top values

Question

I've got data in this form:

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

The user submits a request per call, gets a number of offers each with a value and typically accepts one of them (not always).

I want to know, of the requests where an offer is accepted, which requests had their top value offer accepted and which didn't.

How can I get that information out of this data?

---

I've tried various groupings but can't seem to get it right.

Answer 1

This should be what you're looking for. If not can you update the question with an example of the expected output? Thanks. If you haven't used CTEs before note that the statement before the CTE needs to be terminated with a semicolon.

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;

Answer 2

@cfradenburg, thanks for recasting the data to make it easy to review. However, I think your answer is incorrect. Look at CallNum 971390 and Value 101.34077333330060000000. It has both 'True' and 'False' Accepted rows. If you're comfortable with self-joins this will generate the correct answer.

SELECT
    a.CallNum,
    a.MaxValue as Value,
    max(b.Accepted) as Accepted
FROM 
    (
    SELECT     
        CallNum,
        MAX(Value) as MaxValue,
        MAX(Accepted) as MaxAccepted
    FROM @calldata 
    GROUP BY 
        CallNum
    HAVING
        MAX(Accepted) = 'True'    
    ) a
LEFT OUTER JOIN 
    (
    SELECT     
        CallNum,
        Value,
        Accepted
    FROM @calldata 
    ) b
ON 
    a.CallNum = b.CallNum
    and 
    a.MaxValue = b.Value
GROUP BY
    a.CallNum,
    a.MaxValue

However, It's a little less efficient than the CTE method.

Edit. I corrected a glitch in my self join query if you would want to use it since it's verbose. Also, @cfradenburg I see what's missing in the ORDER BY clause of your window function version of the solution. You need to add this to the ORDER BY clause

, Accepted DESC