Find maximum information using SQL

Question

I've got a table X with the following structure in sql server 2008R2 db (there are >500k records):

Easy enough to find each UserAccountKey's maximum mx :

SELECT 
UserAccountKey
, MAX(mx) mx2
FROM X
GROUP BY UserAccountKey

But I'd like to amend the above so that it also has the SessionId and GamingServerId of the record(s) when the maximum occured.

Here's a work pad in SQL Fiddle. The result I'm after is as follows

I'd rather not have to JOIN on mx if it can be avoided; so I assume a loop is the only way?

Answer 1

You can use a CTE with ROW_NUMBER window-function:

WITH cte AS(
    SELECT 
        RN=ROW_NUMBER()OVER(PARTITION BY UserAccountKey ORDER BY mx DESC),
        UserAccountKey,
        SessionId, 
        GamingServerId,
        mx
    FROM X
)
SELECT  
    UserAccountKey,
    SessionId,
    GamingServerId,
    mx
FROM cte
WHERE RN = 1

Here's your fiddle: http://sqlfiddle.com/#!3/a9e0a/13