Adding a rank to first row of each group

Viewed 62

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:

enter image description here

Here is the initial table:

enter image description here

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.
1 Answers
Related