Adding a rank to first row of each group

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.

---

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;

链接地址: http://www.djcxy.com/p/90784.html

上一篇: 无法在开机时设定重复报警

下一篇: 给每个组的第一行添加一个等级