T-sql Syntax help needed

  • mw_sql_developer

    SSCoach

    Points: 19441

    Question: I want to create a sequential rank.

    Run the code and you will understand what I mean. I need baby5 and baby6 get a 2  and so on.

    The idea is  if we have 8 people with 1oo score all of them get a 1

    Then the 2 folks with 98 get a 2

    97 gets a  3

    95 gets a 4  and so on.

    How can I get that !  Thanks in advance.

     

     

     

    if object_id('tempdb..#t') is not null drop table #t;

    create table #t(name varchar(100), score int );

    INSERT INTO #t(name, score ) values ( 'baby1', 100 );
    INSERT INTO #t(name, score ) values ( 'baby2', 100 );
    INSERT INTO #t(name, score ) values ( 'baby3', 100 );
    INSERT INTO #t(name, score ) values ( 'baby4', 100 );
    INSERT INTO #t(name, score ) values ( 'baby5', 98 );
    INSERT INTO #t(name, score ) values ( 'baby6', 98 );
    INSERT INTO #t(name, score ) values ( 'baby7', 97 );
    INSERT INTO #t(name, score ) values ( 'baby8', 95 );
    INSERT INTO #t(name, score ) values ( 'baby9', 95 );
    INSERT INTO #t(name, score ) values ( 'baby910', 85 );
    INSERT INTO #t(name, score ) values ( 'baby911', 83 );
    INSERT INTO #t(name, score ) values ( 'baby912', 83 );
    INSERT INTO #t(name, score ) values ( 'baby913', 80 );

    ;
    With a as
    (
    Select
    name
    ,RANK() OVER ( order by score desc ) as RNK
    from #t
    )
    Select * from a

     

     

     

     

  • Sue_H

    SSC Guru

    Points: 90695

    Try using DENSE_RANK instead of RANK

    With a as
    (
    Select
    name, score
    ,DENSE_RANK() OVER ( order by score desc ) as RNK
    from #t
    )
    Select * from a

    Sue

  • MiraAniston

    Newbie

    Points: 2

    I tried and it didn’t work for me either. Maybe you have some other solutions? Thanks

  • Jeff Moden

    SSC Guru

    Points: 996809

    MiraAniston wrote:

    I tried and it didn’t work for me either. Maybe you have some other solutions? Thanks

    Saying something as nebulous as "it didn't work" without telling us WHAT about it didn't work doesn't help us help you a bit.

    I tested the code Sue provided with her simple substitution of DENSE_RANK for RANK and it worked just fine.  Since there's no apparent need from what you posted for the CTE, I removed the CTE and it also works just fine.

    Here's the code...

     SELECT  name, score
    ,RNK = DENSE_RANK() OVER (ORDER BY score DESC )
    FROM #t
    ;

    ... and here are the results I got, which are also exactly the same as what Sue's code provided...

    Now, WHAT about the code doesn't actually work for you? 😉  Are you actually using SQL Server or are you using something else?

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply