T-sql Syntax help needed

  • 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

     

     

     

     

  • 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

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    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