Adding ranks to the first row of each group

Question

This brings back 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; 

He returns this:

Here is the source table:

What the script does is:

• Add a new column to the source table
• In the new column, add the game rank for each userAcc with the highest amount.
• Rank is the alphabetical position of the game with the largest amount among user games. Thus, for jas, his highest game is y, and this is in second place among his games.
• The rank found in step 3 should go only against the first alphabetical game of the corresponding user.

Answer 1

For this you do not need to join . 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;