This is returning what I want but is there a simpler, more elegant, approach?
IF OBJECT_ID('TEMPDB..#test') IS NOT NULL DROP TABLE #test;
CREATE TABLE #test
(
userAcc VARCHAR(100),
game VARCHAR(100),
amount INT
);
INSERT INTO #test
values
('jas', 'x', 10),
('jas', 'y', 100),
('jas', 'z', 20),
('sam', 'j', 10),
('sam', 'q', 5);
--initial table sample
SELECT userAcc,
game,
amount
FROM #test;
WITH
X AS
(
SELECT rn = ROW_NUMBER() OVER (PARTITION BY userAcc ORDER BY game),
userAcc,
game,
amount,
rk = RANK() OVER (PARTITION BY userAcc ORDER BY amount DESC)
FROM #test
),
Y AS
(
SELECT RK,userAcc,
game,
targ = rn
FROM X
WHERE rk = 1
)
SELECT X.userAcc,
X.game,
X.amount,
ISNULL(Y.targ,0)
FROM X
LEFT OUTER JOIN Y
ON
X.userAcc = Y.userAcc AND
X.rn = Y.rk
ORDER BY X.userAcc,X.rn;
It returns this:
Here is the initial table:
What the script is doing is this:
- Add a new column to original table
- In new column add the rank of the game for each userAcc with the highest amount.
- The rank is the alphabetical position of the game with the highest amount amongst the user's games. So for jas his highest game is y and that is positioned 2nd amongst his games.
- The rank found in step 3 should only go against the first alphabetical game of the respective user.

