Adding a rank to first row of each group

Question

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:

1. Add a new column to original table
2. In new column add the rank of the game for each userAcc with the highest amount.
3. 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.
4. The rank found in step 3 should only go against the first alphabetical game of the respective user.

Answer 1

You don't need a join for this. You can use accumulation.

If I understand correctly:

 select userAcc, game, amount,
        isnull( (case when rn = 1
               then max(case when rk = 1 then rn end) over (partition by userAcc)
           end),0) as newcol
  from (select t.*,
               ROW_NUMBER() OVER (PARTITION BY userAcc ORDER BY game) as rn,
               RANK() OVER (PARTITION BY userAcc ORDER BY amount DESC) as rk
        from #test t
       ) t
  order by userAcc;